Oracle merge into delete語法

  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 t1
    using (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;