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');
推荐阅读
-
Java中是使用增强for的null问题
-
Oracle数据库中SP和表之间的关系
-
oracle数据库中rman备份归档日志的常用脚本
-
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)的实例