merge into也有delete语法。
sql> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/sql Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
模拟实验：
drop table test1 purge;
drop table test2 purge;create table test1(id number,name varchar2(10));
create table test2(id number,name varchar2(10));
insert into test1 values(1,'a');
insert into test1 values(2,'b');
insert into test1 values(3,'b');
insert into test1 values(4,'b');
insert into test1 values(5,'c');
insert into test1 values(6,'d');
insert into test1 values(7,'e');
insert into test2 values(1,'aa');
insert into test2 values(2,'aa');
insert into test2 values(3,'bb');
create index ind_t1_id on test1(id);
create index ind_t2_id on test2(id);
commit;
sql> select * from test1;
ID NAME
---------- ----------
1 a
2 b
3 b
4 b
5 c
6 d
7 e
sql> select * from test2;
ID NAME
---------- ----------
1 aa
2 aa
3 bb
merge into test1 t1
using (select id from test2 t2 where t2.name = 'aa') t2
on (t1.id = t2.id)
when matched then
update set t1.name = t1.name
delete where t1.id =t2.id;
commit;
sql> select * from test1;
ID NAME
---------- ----------
3 b
4 b
5 c
6 d
7 e
需要注意的是：
sql> merge into test1 t1using (select id from test2 t2 where t2.name = 'aa') t2
on (t1.id = t2.id)
when matched then
delete where t1.id =t2.id;
delete where t1.id =t2.id
*
第 5 行出现错误:
ORA-00905: 缺失关键字
必须要update语句
merge into test1 t1
using (select id from test2 t2 where t2.name = 'aa') t2
on (t1.id = t2.id)
when matched then
update set t1.name = t1.name --where只能出现一次，如果这里使用了where，delete后面的where就无效了。
delete where t1.id =t2.id;
