SQL Server初学者：如何从另一个表更新表数据？

Question

我还在学习SQL。我对更新表格有疑问。如何根据其他表更新我的表？请参阅下面的示例：

create table batch_info (
    batch_key int identity(1, 1) not null primary key,
    batch_num int not null,
    batch_name varchar(50) null,
    batch_start datetime null,
    batch_end datetime null,
    table_name varchar(50) null,
    insert_rows int null
)
go

insert into batch_info (batch_num, batch_name, batch_start)
    values ('32', 'Batch_to_insert_rows', '20110414')
go

select * from batch_info

create table ref_table (
    bat_num int not null,
    bat_end_date datetime null,
    bat_table_name varchar(50) null,
    bat_ins_rows int null,
)
go

insert into ref_table 
    values  ('32','20110414 02:12:00.000','Table1','10'),
            ('32','20110414 02:12:00.000','Table2','33'),
            ('32','20110414 02:12:00.000','Table3','12') 

select * from ref_table

--How can I update batch_info table to get this info?
select 
bi.batch_key, 
bi.batch_num, 
bi.batch_name,
bi.batch_start,
rt.bat_end_date,
rt.bat_table_name, 
rt.bat_ins_rows
 from batch_info as bi
inner join ref_table as rt on bi.batch_num = rt.bat_num

编辑：批处理键是代理项，因此它应该是增量的，而不是像我的联接查询中那样的 (1, 1, 1)。

Answer 1

假设batch_num和bat_end_date的组合是唯一的，那么这应该可以解决问题：

update  batch_info 
set     batch_end = rt.bat_end_date,
        table_name = rt.bat_table_name,
        insert_rows = rt.bat_ins_rows 
from    batch_info bi
        inner join ref_table rt on rt.bat_num = bi.batch_num 
        inner join 
        (
            select  bat_num,
                    MIN(bat_end_date) as min_bat_end_date
            from    ref_table 
        ) oldest on oldest.bat_num = rt.bat_num 
                    and oldest.min_bat_end_date = rt.bat_end_date 
where   bi.batch_end is null

insert into batch_info 
(batch_num, batch_name, batch_start, batch_end, table_name, insert_rows)
select  bi.batch_num,
        bi.batch_name,
        bi.batch_start,
        rt.bat_end_date,
        rt.bat_table_name,
        rt.bat_ins_rows 
from    batch_info bi
        inner join ref_table rt on rt.bat_num = bi.batch_num 
where   not exists 
        (
            select   * 
            from     batch_info e 
            where    e.batch_num = bi.batch_num 
                     and e.batch_end = rt.bat_end_date
        )

如果另一个值的组合建立了唯一性，那么可以将它们添加到第一个查询中的oldest，并且e< /code> 在第二个。

我同意@Jim 的观点，即这应该分为两个表。执行此操作的难度预示着此设计将造成的困难。您试图将两个不同的事物（批次和批次运行）放入一个表中，这绝不是一个好主意。

Answer 2

insert into batch_info
SELECT batch_num, batch_name, batch_start,
bat_end, bat_table_name, bat_ins_rows
FROM batch_info inner join ref_table
on batch_info.batch_num = ref_table.bat_num

delete from batch_info
where batch_end is null

编辑：INSERT 正在生成所有匹配的行&重新插入。新的身份值将添加到其中。现有行（其他字段为空）用于 INSERT 语句 &后来使用第二条语句删除。

Answer 3

我本打算向您指出合并声明，但我不这样做也不认为这可以用那个来完成。我认为您将无法使用 光标。

话虽如此，但不知道您现有的要求，它确实应该是两个表。将所有内容推入一张表会使表设计非规范化。

Answer 4

使batch_info.batch_key字段自动递增。

create table batch_info (
batch_key int identity(1, 1) not null primary key, -- <<- make autoincrement.
...