導(dǎo)讀:1建站知識(shí)因?yàn)轫?xiàng)目要求實(shí)現(xiàn)一次性同時(shí)更新多條不同的記錄的需求，和同事討論了一個(gè)比較不錯(cuò)的方案，這里供大家參考下網(wǎng)站優(yōu)化seo培訓(xùn)網(wǎng)站seo優(yōu)化軟件。

以下為測(cè)試?yán)印?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) t2on t1.id = t2.idwhen matched thenupdate set t1.age = t2.age

 

是不是挺有趣的Sql。如何一次性更新多條不同值的記錄標(biāo)題可能沒說(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ì)劃 -網(wǎng)站seo優(yōu)化軟件--------------------------------------------------------- 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如何seo優(yōu)化推廣網(wǎng)站"."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 | -----------------------------------------------seo網(wǎng)站排名優(yōu)化軟件----------------------------- | 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)鍵詞標(biāo)簽: 不同 多條