merge into delete的语法
来源:互联网 发布:毁灭战士4优化 贴吧 编辑:程序博客网 时间:2024/06/06 00:25
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;
阅读全文
0 0
- merge into delete的语法
- Oracle merge into delete语法
- merge...into 语法的使用
- Oracle的Merge into语法
- Merge Into 语法
- db2 merge into语法
- MERGE INTO语法
- merge into 语法
- Oracle9I新增语法 merge into
- Oracle语法:merge into using
- Oracle merge into语法简介
- merge into的用法
- merge into 的用法
- merge into的用法
- oracle的merge into
- Merge into 的使用
- merge into的用法
- merge into的妙用
- Android Telephony分析(一) ---- Phone详解
- sql中count(0),count(1),count(),count(列名)
- tomcat服务器配置字符集为utf-8-彻底解决中文乱码问题
- 仿饿了么 问题集
- libuv download
- merge into delete的语法
- nginx 配置https 并兼容http 或强制http 转为https
- fragment在activity里面实例化
- properties文件的配置加载
- Android Studio的Gradle的加速
- ionic开发——打开百度地图APP如何传递参数,导航路线
- C++boost库之assign使用(一)
- js中的事件委托
- SQL——存储过程