多表關(guān)聯(lián)同時(shí)更新多條不同的記錄方法分享
更新時(shí)間：2011年10月31日 23:47:43 作者：
因?yàn)轫?xiàng)目要求實(shí)現(xiàn)一次性同時(shí)更新多條不同的記錄的需求，和同事討論了一個(gè)比較不錯(cuò)的方案，這里供大家參考下
以下為測(cè)試?yán)印?
1.首先創(chuàng)建兩張臨時(shí)表并錄入測(cè)試數(shù)據(jù)：
create table #temptest1
(
id int,
name1 varchar(50),
age int
)
create table #temptest2
(
id int,
name1 varchar(50),
age int
)
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)
)
現(xiàn)有記錄
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來(lái)對(duì)應(yīng)。
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行。
已用時(shí)間: 00: 00: 00.01
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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行。
已用時(shí)間: 00: 00: 00.00
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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行。
已用時(shí)間: 00: 00: 00.01
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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
1.首先創(chuàng)建兩張臨時(shí)表并錄入測(cè)試數(shù)據(jù)：
復(fù)制代碼 代碼如下:
create table #temptest1
(
id int,
name1 varchar(50),
age int
)
create table #temptest2
(
id int,
name1 varchar(50),
age int
)
查詢出此時(shí)的表數(shù)據(jù)為：
#temptest1 #temptest2
2.現(xiàn)在要將#temptest2中的年齡更新到相應(yīng)的#temptest1中的年齡。
其實(shí)就是讓[表1]中ID為1的年齡改成19，同時(shí)ID為2的年齡改成20。
當(dāng)然這里的要求是只用一句SQL，不能用循環(huán)。
結(jié)果如下：
實(shí)現(xiàn)方法如下：
Update t1
Set t1 .age = t2.age
From #temptest1 t1
Join #temptest2 t2
On t1.id = t2.id
(補(bǔ)充)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。
如何一次性更新多條不同值的記錄
標(biāo)題可能沒(méi)說(shuō)清楚，假設(shè)有這樣兩張表：
復(fù)制代碼 代碼如下:
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)
)
現(xiàn)有記錄
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來(lái)對(duì)應(yīng)。
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行。
已用時(shí)間: 00: 00: 00.01
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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行。
已用時(shí)間: 00: 00: 00.00
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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行。
已用時(shí)間: 00: 00: 00.01
執(zhí)行計(jì)劃
----------------------------------------------------------
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)
統(tǒng)計(jì)信息
----------------------------------------------------------
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
相關(guān)文章
開發(fā)人員需要應(yīng)用SQL語(yǔ)句來(lái)構(gòu)建數(shù)據(jù)庫(kù)模型,測(cè)試人員需要用SQL語(yǔ)句來(lái)操作數(shù)據(jù)庫(kù)數(shù)據(jù),到底SQL語(yǔ)句的查詢執(zhí)行順序是怎樣的呢,本文就來(lái)介紹一下2023-09-09
SQL?Server中實(shí)現(xiàn)錯(cuò)誤處理
這篇文章介紹了SQL?Server中實(shí)現(xiàn)錯(cuò)誤處理的方法，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-05-05
MSSQL優(yōu)化之探索MSSQL執(zhí)行計(jì)劃（轉(zhuǎn)）
最近總想整理下對(duì)MSSQL的一些理解與感悟，卻一直沒(méi)有心思和時(shí)間寫，晚上無(wú)事便寫了一篇探索MSSQL執(zhí)行計(jì)劃,本文講執(zhí)行計(jì)劃但不僅限于講執(zhí)行計(jì)劃2011-10-10
SQL Server格式轉(zhuǎn)換函數(shù)Cast、Convert介紹
這篇文章介紹了SQL Server中的格式轉(zhuǎn)換函數(shù)Cast、Convert，對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)學(xué)習(xí)吧2022-05-05
這篇文章介紹了查詢存儲(chǔ)過(guò)程中特定字符的方法，有需要的朋友可以參考一下2013-09-09
SQL語(yǔ)句導(dǎo)入導(dǎo)出大全...2006-11-11
SQL Server 數(shù)據(jù)太多優(yōu)化的方法
本文介紹了幾種優(yōu)化SQLServer數(shù)據(jù)庫(kù)性能的方法,包括索引優(yōu)化、數(shù)據(jù)分區(qū)和分表、數(shù)據(jù)歸檔、存儲(chǔ)和硬件優(yōu)化、數(shù)據(jù)庫(kù)參數(shù)和配置優(yōu)化、批量數(shù)據(jù)處理、清理無(wú)用數(shù)據(jù)、使用緩存、并行查詢與并發(fā)以及SQLServer實(shí)例優(yōu)化,這些方法可以幫助在處理大量數(shù)據(jù)時(shí)保持較好的性能2024-11-11
SQL Server中使用判斷語(yǔ)句（IF ELSE/CASE WHEN ）案例
這篇文章主要介紹了SQL Server中使用判斷語(yǔ)句（IF ELSE/CASE WHEN ）案例,本篇文章通過(guò)簡(jiǎn)要的案例,講解了該項(xiàng)技術(shù)的了解與使用,以下就是詳細(xì)內(nèi)容,需要的朋友可以參考下2021-07-07
本篇文章是對(duì)如何用SQL語(yǔ)句在指定字段前面插入新的字段的方法進(jìn)行了詳細(xì)的分析介紹，需要的朋友參考下2013-06-06
MySql安裝到最后一步的時(shí)候，出現(xiàn)1045的錯(cuò)誤有兩種解決辦法。2009-07-07