1、建立测试表
SQL> select * from test1;
ID NAME
----------- --------------------
1 gaopeng
2 gaopeng1
3 gaopeng2
4 gaopeng3
SQL> select * from test2;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
2、目标 根据ID进行更改将TEST1中的name字段更改为TEST2中的NAME字段
3、写法
---UPDATE错误写法
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) ;
结果为
SQL> update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) ;
4 rows updated
SQL> select * from test1;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
3
4
可以看到TEST1中ID不与TEST2中匹配的行赋予了正确的值，不匹配的行赋予了空值，这不是我们需要的。
---UPDATE正确的写法
SQL> update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id)
2 where exists (select 1
3 from test2 t2
4 where t1.id=t2.id
5 )
6 ;
2 rows updated
SQL> select * from test1;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
3 gaopeng2
4 gaopeng3
可以看到这种写法达到了我们的要求，因为首先通过EXISTS语句过滤掉了不匹配的行，如果需要对TEST1表进行WHERE 条件限制可以如下：
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id)
where exists (select 1
from test2 t2
where t1.id=t2.id
)
and t1.id=1;
---MERGE正确写法
SQL> merge into test1 t1
2 using
3 test2 t2
4 on(t1.id=t2.id)
5 when matched then
6 update
7 set t1.name=t2.name
8 ;
2 rows merged
SQL> select * from test1 order by id;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
3 gaopeng2
4 gaopeng3
可以看到这种写法也达到了我们要的求。如果需要对TEST1表进行限制条件可以
merge into test1 t1
using
test2 t2
on(t1.id=t2.id)
when matched then
update
set t1.name=t2.name
where t1.id=2;
注意：对于如上的更改的要求TEST2表中ID列必须唯一键
如果不唯一都会报错
在TEST2表中增加一行
SQL> insert into test2 values(1,'yanlei1');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test2;
ID NAME
----------- --------------------
2 yanlei1
1 yanlei
1 yanlei1
update 会报错
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id)
where exists (select 1
from test2 t2
where t1.id=t2.id
)
ORA-01427: single-row subquery returns more than one row
merger 会报错
ORA-30926: unable to get a stable set of rows in the source tables
对于执行性能而言：
MERGE 可以对执行计划进行控制，更改其连接方式提高性能，但是UPDATE首先会做WHERE条件后的子查询，通过关联子查询过滤出相应的行数（关联子查询由外层表驱动内层表
换句话说外层表有多少行就会驱动多少次，虽然ORACLE做了SEMI ANTI等半连接但性能还是大数据量的情况下任然很慢），然后通过查询出来的行数和
更改表做连接进行更改数据(次连接为NEST LOOP类型），不仅多一个步骤而且无法通过更改连接方式进行提高性能。
UPDATE执行计划：
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 32 |
| 1 | UPDATE | TEST1 | 1 | | 0 |00:00:00.01 | 32 | --第二次驱动
|* 2 | HASH JOIN SEMI | | 1 | 2 | 2 |00:00:00.01 | 14 | --第一次驱动
| 3 | TABLE ACCESS FULL| TEST1 | 1 | 4 | 4 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| TEST2 | 1 | 2 | 2 |00:00:00.01 | 7 |
|* 5 | TABLE ACCESS FULL | TEST2 | 2 | 1 | 2 |00:00:00.01 | 14 |
---------------------------------------------------------------------------------------
MERGE执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520388833
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2 | 100 | 7 (15)| 00:00:01 |
| 1 | MERGE | TEST1 | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 2 | 124 | 7 (15)| 00:00:01 | --仅驱动一次，这一步是可以使用HINT更改连接方式的
| 4 | TABLE ACCESS FULL| TEST2 | 2 | 50 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TEST1 | 4 | 148 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
SQL> select * from test1;
ID NAME
----------- --------------------
1 gaopeng
2 gaopeng1
3 gaopeng2
4 gaopeng3
SQL> select * from test2;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
2、目标 根据ID进行更改将TEST1中的name字段更改为TEST2中的NAME字段
3、写法
---UPDATE错误写法
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) ;
结果为
SQL> update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id) ;
4 rows updated
SQL> select * from test1;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
3
4
可以看到TEST1中ID不与TEST2中匹配的行赋予了正确的值，不匹配的行赋予了空值，这不是我们需要的。
---UPDATE正确的写法
SQL> update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id)
2 where exists (select 1
3 from test2 t2
4 where t1.id=t2.id
5 )
6 ;
2 rows updated
SQL> select * from test1;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
3 gaopeng2
4 gaopeng3
可以看到这种写法达到了我们的要求，因为首先通过EXISTS语句过滤掉了不匹配的行，如果需要对TEST1表进行WHERE 条件限制可以如下：
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id)
where exists (select 1
from test2 t2
where t1.id=t2.id
)
and t1.id=1;
---MERGE正确写法
SQL> merge into test1 t1
2 using
3 test2 t2
4 on(t1.id=t2.id)
5 when matched then
6 update
7 set t1.name=t2.name
8 ;
2 rows merged
SQL> select * from test1 order by id;
ID NAME
----------- --------------------
1 yanlei
2 yanlei1
3 gaopeng2
4 gaopeng3
可以看到这种写法也达到了我们要的求。如果需要对TEST1表进行限制条件可以
merge into test1 t1
using
test2 t2
on(t1.id=t2.id)
when matched then
update
set t1.name=t2.name
where t1.id=2;
注意：对于如上的更改的要求TEST2表中ID列必须唯一键
如果不唯一都会报错
在TEST2表中增加一行
SQL> insert into test2 values(1,'yanlei1');
1 row inserted
SQL> commit;
Commit complete
SQL> select * from test2;
ID NAME
----------- --------------------
2 yanlei1
1 yanlei
1 yanlei1
update 会报错
update test1 t1 set name=(select name from test2 t2 where t1.id=t2.id)
where exists (select 1
from test2 t2
where t1.id=t2.id
)
ORA-01427: single-row subquery returns more than one row
merger 会报错
ORA-30926: unable to get a stable set of rows in the source tables
对于执行性能而言：
MERGE 可以对执行计划进行控制，更改其连接方式提高性能，但是UPDATE首先会做WHERE条件后的子查询，通过关联子查询过滤出相应的行数（关联子查询由外层表驱动内层表
换句话说外层表有多少行就会驱动多少次，虽然ORACLE做了SEMI ANTI等半连接但性能还是大数据量的情况下任然很慢），然后通过查询出来的行数和
更改表做连接进行更改数据(次连接为NEST LOOP类型），不仅多一个步骤而且无法通过更改连接方式进行提高性能。
UPDATE执行计划：
---------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1 | | 0 |00:00:00.01 | 32 |
| 1 | UPDATE | TEST1 | 1 | | 0 |00:00:00.01 | 32 | --第二次驱动
|* 2 | HASH JOIN SEMI | | 1 | 2 | 2 |00:00:00.01 | 14 | --第一次驱动
| 3 | TABLE ACCESS FULL| TEST1 | 1 | 4 | 4 |00:00:00.01 | 7 |
| 4 | TABLE ACCESS FULL| TEST2 | 1 | 2 | 2 |00:00:00.01 | 7 |
|* 5 | TABLE ACCESS FULL | TEST2 | 2 | 1 | 2 |00:00:00.01 | 14 |
---------------------------------------------------------------------------------------
MERGE执行计划
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 520388833
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 2 | 100 | 7 (15)| 00:00:01 |
| 1 | MERGE | TEST1 | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 2 | 124 | 7 (15)| 00:00:01 | --仅驱动一次，这一步是可以使用HINT更改连接方式的
| 4 | TABLE ACCESS FULL| TEST2 | 2 | 50 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| TEST1 | 4 | 148 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------
谢谢！
