Oracle merge into delete语法

发布时间：2020-12-12 16:34:19 所属栏目：百科来源：网络整理

导读：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.

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;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!