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