Oracle 10g中增强的Merge
创建测试表CREATE TABLE test1 ASSELECT *FROM all_objectsWHERE 1=2;MATCHED和NOT MATCHED子句均为可选项 ①两个子句都出现.
创建测试表
CREATE TABLE test1 AS
SELECT *
FROM all_objects
WHERE 1=2;
MATCHED和NOT MATCHED子句均为可选项
①两个子句都出现.
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
②只有插入操作,即只有NOT MATCHED子句
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status);
③只有更新操作,即只有MATCHED子句
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status;
源表可以包含WHERE子句
①更新和插入均包含WHERE子句
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE b.status != 'VALID';
②插入操作包含WHERE子句
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN NOT MATCHED THEN
INSERT (object_id, status)
VALUES (b.object_id, b.status)
WHERE b.status != 'VALID';
③更新操作包含WHERE子句
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID';
DELETE子句
可以使用该子句删除源表中同时满足ON子句条件及DELETE WHERE子句条件的记录。
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
DELETE WHERE (b.status = 'VALID');
下一篇: vi 命令大全
推荐阅读
-
oracle中的trim函数使用介绍
-
VMware中linux环境下oracle安装图文教程(二)ORACLE 10.2.05版本的升级补丁安装
-
linux下oracle报错,提示ORA-12514: TNS: 监听程序当前无法识别连接描述符中请求的服务的解决办法
-
Oracle Form中COMMIT的概述及使用技巧
-
oracle中left join和right join的区别浅谈
-
Oracle中在pl/sql developer修改表的2种方法
-
在Pandas中DataFrame数据合并,连接(concat,merge,join)的实例
-
解析php中array_merge与array+array的区别
-
Oracle性能优化——SQL基线(SQLbaseline)的载入与进化(11g中引入的基线)
-
Oracle 11gR2中启动Scott用户的方法(推荐)