服务器之家:专注于服务器技术及软件下载分享
分类导航

Mysql|Sql Server|Oracle|Redis|MongoDB|PostgreSQL|Sqlite|DB2|mariadb|Access|数据库技术|

服务器之家 - 数据库 - Sql Server - 多表关联同时更新多条不同的记录方法分享

多表关联同时更新多条不同的记录方法分享

2019-12-10 14:42MSSQL教程网 Sql Server

因为项目要求实现一次性同时更新多条不同的记录的需求,和同事讨论了一个比较不错的方案,这里供大家参考下

以下为测试例子。 
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

On  t1.id = t2.id

 

(补充)Sql Server 2008 Merge命令写法:

merge into #temptest1 t1 
using(select age,id from #temptest2) t2
on t1.id = t2.id
when matched then
update 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 on 
SQL> 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 calls 
4 db block gets 
23 consistent gets 
0 physical reads 
1004 redo size 
840 bytes sent via SQL*Net to client 
856 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
1 sorts (memory) 
0 sorts (disk) 
4 rows processed 
SQL> 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 calls 
1 db block gets 
53 consistent gets 
0 physical reads 
588 redo size 
840 bytes sent via SQL*Net to client 
858 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
1 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 calls 
4 db block gets 
17 consistent gets 
0 physical reads 
1004 redo size 
840 bytes sent via SQL*Net to client 
827 bytes received via SQL*Net from client 
3 SQL*Net roundtrips to/from client 
3 sorts (memory) 
0 sorts (disk) 
4 rows processed

延伸 · 阅读

精彩推荐