Plan hash value: 1761655026
1004 redo size
1 d 四
840 bytes sent via SQL*Net to client
----------------------------------------------------------------------------

id number,
SQL> update (select ta.b tab1 ,tb.b tbb from ta,tb where ta.a=tb.a) set tab1=tbb;
=================
0 physical reads
chi varchar2(3)，
8 recursive calls
0 sorts (disk)

11 recursive calls
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
0 physical reads
testA：
----------------------------------------------------------

复制代码 代码如下:

3 sorts (memory)
标题可能没说清楚，假设有这样两张表：
3 SQL*Net roundtrips to/from client
age int

From  #temptest1 t1

| 3 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
1 sorts (memory)

create table testB(
)
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);

|* 6 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
(
eng varchar2(3),
840 bytes sent via SQL*Net to client
testB:
2 - access("TA"."A"="TB"."A")
| 1 | UPDATE | TA | | | | |
| 0 | UPDATE STATEMENT | | 5 | 165 | 20 (30)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TB | 4 | 52 | 2 (0)| 00:00:01 |
Predicate Information (identified by operation id):
已用时间: 00: 00: 00.00
|* 4 | TABLE ACCESS FULL| TB | 1 | 13 | 2 (0)| 00:00:01 |
2 - access("TA"."A"="ITEM_1")

)
0 sorts (disk)

实现方法如下：

2 - filter("TA"."A"= (SELECT "TB"."A" FROM "TB" "TB" WHERE
age int
53 consistent gets
--------------------------------------------------------------------------------
3 c 三
执行计划
----------------------------------------------------------------------------
---------------------------------------------------
1004 redo size
1 sorts (memory)

复制代码 代码如下:

17 consistent gets
|* 2 | FILTER | | | | | |
ID ENG CHI
create table testA(
)
4 db block gets

当然这里的要求是只用一句SQL，不能用循环。

Plan hash value: 1137212925
CODE:
588 redo size
已更新4行。
| 1 | UPDATE | TA | | | | |

name1 varchar(50),
827 bytes received via SQL*Net from client
--------------------------------------------------------------------------------
我想把testB中的记录的ENG,CHI字段更新到testA中去，以ID来对应。
如果 create unique index tb_a_uidx on tb(a);
----------------------------------------------------------

[Copy to clipboard] [ - ]
----------------------------------------------------------------------------
ID ENG CHI ANY....
-----
Note

- dynamic sampling used for this statement (level=2)
update set t1.age = t2.age

----------------------------------------------------------
已更新4行。
858 bytes received via SQL*Net from client
6 - filter("TB"."A"=:B1)

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

---------------------------------------------------

merge into #temptest1 t1
id int,
如何一次性更新多条不同值的记录
3 SQL*Net roundtrips to/from client
统计信息
anythingother varchar2(1)
现有记录



----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
)

结果如下：

是不是挺有趣的Sql。
3 f 六
----------------------------------------------------------
| 4 | TABLE ACCESS FULL| TA | 5 | 100 | 2 (0)| 00:00:01 |
5 - filter("TB"."A"=:B1)
23 consistent gets
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);
|* 2 | HASH JOIN | | 4 | 184 | 5 (20)| 00:00:01 |
统计信息
- dynamic sampling used for this statement (level=2)
执行计划
3 SQL*Net roundtrips to/from client
4 rows processed

----------------------------------------------------------
以下为测试例子。
using(select age,id from #temptest2) t2
---------------------------------------------------
===============
(
统计信息
----------------------------------------------------------
Predicate Information (identified by operation id):
0 recursive calls
Plan hash value: 3571861550
4 rows processed
0 sorts (disk)
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
id int,
| 4 | VIEW | VW_SQ_1 | 4 | 52 | 2 (0)| 00:00:01 |
2 e 五
id number,
| 3 | TABLE ACCESS FULL| TB | 4 | 104 | 2 (0)| 00:00:01 |
856 bytes received via SQL*Net from client

| 3 | TABLE ACCESS FULL | TA | 5 | 100 | 2 (0)| 00:00:01 |

On  t1.id = t2.id

2.现在要将#temptest2中的年龄更新到相应的#temptest1中的年龄。

Note
| 0 | UPDATE STATEMENT | | 1 | 20 | 7 (15)| 00:00:01 |
--------------------------------------------------------------------------------
1.首先创建两张临时表并录入测试数据：
-----
create table #temptest1
1 db block gets
|* 2 | HASH JOIN SEMI | | 5 | 165 | 5 (20)| 00:00:01 |

Set t1 .age = t2.age

Note
已用时间: 00: 00: 00.01
4 db block gets

其实就是让[表1]中ID为1的年龄改成19，同时ID为2的年龄改成20。

SQL> set autot on

chi varchar2(3)
0 physical reads

Join #temptest2 t2

查询出此时的表数据为：

4 - filter("TB"."A"=:B1)
|* 5 | TABLE ACCESS FULL | TB | 1 | 26 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------
eng varchar2(3),
-----

Update t1 

840 bytes sent via SQL*Net to client
1 a 一
| 1 | UPDATE | TA | | | | |
CODE:
4 rows processed
执行计划
| 0 | UPDATE STATEMENT | | 4 | 184 | 5 (20)| 00:00:01 |
Predicate Information (identified by operation id):

#temptest1                 #temptest2

2 b 二
已更新4行。
create table #temptest2
已用时间: 00: 00: 00.01
name1 varchar(50),
- dynamic sampling used for this statement (level=2)
"TB"."A"=:B1))
----------------------------------------------------------------------------
when matched then
on t1.id = t2.id