MERGE语句是Oracle9i新增的语法,用来合并UPDATE和INSERT语句。通过MERGE语句,根据一张表或子查询的连接条件对另外一张表进行查询,连接条件匹配上的进行UPDATE,无法匹配的执行INSERT。这个语法仅需要一次全表扫描就完成了全部工作,执行效率要高于INSERT+UPDATE
WITH upsert AS (
UPDATE test1
SET col1 = test2.col1
FROM test2
WHERE test1.id = test2.id
RETURNING test1.*
)
INSERT INTO test01
SELECT *
FROM test2
WHERE NOT EXISTS (
SELECT 1
FROM upsert b
WHERE test2.id = b.id
);
oracle一张表向另一张表插入不重复数据merge into用法
MERGEINTO TEST A USING TEST B
ON (A.NAME=B.NAME)
WHEN MATCHEDTHEN
UPDATE SET A.SEX='女'WHERE A.NAME='SUNZHENXING'
WHEN NOT MATCHEDTHEN
INSERT VALUES (3,'SUNZHENXING','女')