sql 联合更新、关联更新

1、【如果存在测试临时表，则先删除，便于重复执行SQL】：IF OBJECT_ID('tempdb..#SourceTable1') IS NOT NULL DROP TABLE #SourceTable1;IF OBJECT_ID('tempdb..#SourceTable2') IS NOT NULL DROP TABLE #SourceTable2;IF OBJECT_ID('tempdb..#UpdateTable') IS NOT NULL DROP TABLE #UpdateTable;

2、【创建源表1】：CREATE TABLE #SourceTable1( Id UNIQUEIDENTIFIER, SCode VARCHAR(50), SName NVARCHAR(100), Remark NVARCHAR(500))

3、【创建源表2】：CREATE TABLE #SourceTable2( Id UNIQUEIDENTIFIER, SCode VARCHAR(50), SAttr1 VARCHAR(100), SAttr2 VARCHAR(100))

4、【创建需要更新数据的表】：CREATE TABLE #UpdateTable( Id UNIQUEIDENTIFIER, SCode VARCHAR(50), SName NVARCHAR(100), SAttr1 VARCHAR(100), SAttr2 VARCHAR(100))

5、【插入源表1测试数据】：INSER哌囿亡噱T INTO #SourceTable1(Id, SCode, SName, Remark) VALUES(NEWID(), 'SCode1', '更新名字1', '更新测试备注1');INSERT INTO #SourceTable1(Id, SCode, SName, Remark) VALUES(NEWID(), 'SCode2', '更新名字2', '更新测试备注2');INSERT INTO #SourceTable1(Id, SCode, SName, Remark) VALUES(NEWID(), 'SCode3', '更新名字3', '更新测试备注3'); -- 查询插入的结果 SELECT * FROM #SourceTable1

6、【插入源表2测试数据】：INSERT INTO #SourceTable2(Id, SCode, SAttr1, SAttr2) VALUES(NEWID(), 'SCode1', 'SCode1-更新属性1', 'SCode1-更新属性2');INSERT INTO #SourceTable2(Id, SCode, SAttr1, SAttr2) VALUES(NEWID(), 'SCode2', 'SCode2-更新属性1', 'SCode2-更新属性2'); -- 查询插入的结果 SELECT * FROM #SourceTable2

7、【插入#UpdateTable的原奘疚豫枭始记录】：INSERT INTO #UpdateTable(Id, SCode, SName, SAttr1, SAttr2) VALUES(NEWI肛舀辨乔D(), 'SCode1', '名字1', 'SCode1-属性1', 'SCode1-属性1');INSERT INTO #UpdateTable(Id, SCode, SName, SAttr1, SAttr2) VALUES(NEWID(), 'SCode2', '名字2', 'SCode2-属性2', 'SCode2-属性2');INSERT INTO #UpdateTable(Id, SCode, SName, SAttr1, SAttr2) VALUES(NEWID(), 'SCode3', '名字3', 'SCode3-属性3', 'SCode3-属性3'); -- 查询插入的结果 SELECT * FROM #UpdateTable

8、【使用联合查询，将#UpdateTable、源表1、2中相同SCode的行字段更新到表#UpdateTable】：UPDATE utSET ut.SName = sc1.SName, ut.SAttr1 = sc2.SAttr1, ut.SAttr2 = sc2.SAttr2FROM #UpdateTable ut, #SourceTable1 sc1, #SourceTable2 sc2WHERE ut.SCode = sc1.SCodeAND ut.SCode = sc2.SCode -- 查询插入的结果 SELECT * FROM #UpdateTable