SQL Server 多表关联同时更新多条不同的记录方法
2022-11-12 09:49:24
内容摘要
这篇文章主要为大家详细介绍了SQL Server 多表关联同时更新多条不同的记录方法，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！以下为测试
文章正文
这篇文章主要为大家详细介绍了SQL Server 多表关联同时更新多条不同的记录方法，具有一定的参考价值，可以用来参考一下。
对此感兴趣的朋友，看看idc笔记做的技术笔记！
以下为测试例子。1.首先创建两张临时表并录入测试数据：代码如下:
create table #temptest1
(
id int,
name1 varchar(50),
age int
)
create table #temptest2
(
id int,
name1 varchar(50),
age int
)
查询出此时的表数据为：
#temptest1 #temptest2
【图片暂缺】 【图片暂缺】
2.现在要将#temptest2中的年龄更新到相应的#temptest1中的年龄。
其实就是让[表1]中ID为1的年龄改成19，同时ID为2的年龄改成20。
当然这里的要求是只用一句SQL，不能用循环。
结果如下：
【图片暂缺】
实现方法如下：
Update t1
Set t1.age = t2.age
From#temptest1 t1
Join #temptest2 t2
Ont1.id = t2.id
(补充)Sql Server 2008 Merge命令写法：
merge into #temptest1 t1using(select age,id from #temptest2) t2on t1.id = t2.idwhen matched thenupdate set t1.age = t2.age
是不是挺有趣的Sql。如何一次性更新多条不同值的记录标题可能没说清楚，假设有这样两张表：
代码如下:
create table testA(
id number,
eng varchar2(3),
chi varchar2(3)
)
create table testB(
id number,
eng varchar2(3),
chi varchar2(3)，
anythingother varchar2(1)
)
现有记录testA：ID ENG CHI===============1 a 一2 b 二3 c 三testB:ID ENG CHI ANY....=================1 d 四2 e 五3 f 六我想把testB中的记录的ENG,CHI字段更新到testA中去，以ID来对应。CODE:SQL> set autot onSQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where exists (select 1 from tb where ta.a=tb.a);已更新4行。已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 1137212925--------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 5 | 165 | 20 (30)| 00:00:01 || 1 | UPDATE | TA | | | | ||* 2 | HASH JOIN SEMI | | 5 | 165 | 5 (20)| 00:00:01 || 3 | TABLE ACCESS FULL | TA | 5 | 100 | 2 (0)| 00:00:01 || 4 | VIEW | VW_SQ_1 | 4 | 52 | 2 (0)| 00:00:01 || 5 | TABLE ACCESS FULL| TB | 4 | 52 | 2 (0)| 00:00:01 ||* 6 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |--------------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("TA"."A"="ITEM_1")6 - filter("TB"."A"=:B1)Note------ dynamic sampling used for this statement (level=2)统计信息----------------------------------------------------------0 recursive calls4 db block gets23 consistent gets0 physical reads1004 redo size840 bytes sent via SQL*Net to client856 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)4 rows processedSQL> update ta set ta.b=(select tb.b from tb where ta.a=tb.a) where ta.a= (select tb.a from tb where ta.a=tb.a);已更新4行。已用时间: 00: 00: 00.00执行计划----------------------------------------------------------Plan hash value: 3571861550----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 || 1 | UPDATE | TA | | | | ||* 2 | FILTER | | | | | || 3 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 ||* 4 | TABLE ACCESS FULL| TB | 1 | 13 | 2 (0)| 00:00:01 ||* 5 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - filter("TA"."A"= (SELECT "TB"."A" FROM "TB" "TB" WHERE"TB"."A"=:B1))4 - filter("TB"."A"=:B1)5 - filter("TB"."A"=:B1)Note------ dynamic sampling used for this statement (level=2)统计信息----------------------------------------------------------11 recursive calls1 db block gets53 consistent gets0 physical reads588 redo size840 bytes sent via SQL*Net to client858 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client1 sorts (memory)0 sorts (disk)4 rows processed如果 create unique index tb_a_uidx on tb(a);[Copy to clipboard] [ - ]CODE:SQL> update (select ta.b tab1 ,tb.b tbb from ta,tb where ta.a=tb.a) set tab1=tbb;已更新4行。已用时间: 00: 00: 00.01执行计划----------------------------------------------------------Plan hash value: 1761655026----------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |----------------------------------------------------------------------------| 0 | UPDATE STATEMENT | | 4 | 184 | 5 (20)| 00:00:01 || 1 | UPDATE | TA | | | | ||* 2 | HASH JOIN | | 4 | 184 | 5 (20)| 00:00:01 || 3 | TABLE ACCESS FULL| TB | 4 | 104 | 2 (0)| 00:00:01 || 4 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |----------------------------------------------------------------------------Predicate Information (identified by operation id):---------------------------------------------------2 - access("TA"."A"="TB"."A")Note------ dynamic sampling used for this statement (level=2)统计信息----------------------------------------------------------8 recursive calls4 db block gets17 consistent gets0 physical reads1004 redo size840 bytes sent via SQL*Net to client827 bytes received via SQL*Net from client3 SQL*Net roundtrips to/from client3 sorts (memory)0 sorts (disk)4 rows processed
注：关于SQL Server 多表关联同时更新多条不同的记录方法的内容就先介绍到这里，更多相关文章的可以留意
代码注释
作者：喵哥笔记
