oracle merge into ora 00905,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 t1using (select id from test2 t2 where t2.name = 'aa') t2on (t1.id = t2.id)when matched thenupdate  set t1.name = t1.name  --where只能出现一次，如果这里使用了where，delete后面的where就无效了。delete where t1.id =t2.id;