Oracle merge into delete语法

蔚落 2022-09-30 09:41 9阅读 0赞

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;