SQL Server初学者：如何从另一个表更新表数据？
|
我仍在学习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
没有找到相关结果
已邀请:
4 个回复
完趣镐
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 )。 我同意@Jim认为应该将其分为两个表。执行此操作的困难预示了该设计将要创建的困难。您试图将两个不同的东西（批处理和批处理运行）放入一个表中，这从来都不是一个好主意。宠封钞轰
编辑：ѭ5会生成所有匹配的行并重新插入。新的身份值将添加到这些值中。现有行（其他字段为null）用于“ 5”语句，随后使用第二条语句删除。臂哦
疾桓