该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据。ORACLE 9i中，使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。
举例：
准备数据：
create table PRODUCTS ( PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) ); insert into PRODUCTS values (1501, ‘VIVITAR 35MM‘, ‘ELECTRNCS‘); insert into PRODUCTS values (1502, ‘OLYMPUS IS50‘, ‘ELECTRNCS‘); insert into PRODUCTS values (1600, ‘PLAY GYM‘, ‘TOYS‘); insert into PRODUCTS values (1601, ‘LAMAZE‘, ‘TOYS‘); insert into PRODUCTS values (1666, ‘HARRY POTTER‘, ‘DVD‘); commit; create table NEWPRODUCTS ( PRODUCT_ID INTEGER, PRODUCT_NAME VARCHAR2(60), CATEGORY VARCHAR2(60) ); insert into NEWPRODUCTS values (1502, ‘OLYMPUS CAMERA‘, ‘ELECTRNCS‘); insert into NEWPRODUCTS values (1601, ‘LAMAZE‘, ‘TOYS‘); insert into NEWPRODUCTS values (1666, ‘HARRY POTTER‘, ‘TOYS‘); insert into NEWPRODUCTS values (1700, ‘WAIT INTERFACE‘, ‘BOOKS‘); commit;
1、可省略的update 或者insert
使用表newproducts中的product_name 和category字段来更新表products 中相同product_id的product_name 和category.
MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category;
当条件不满足的时候把newproducts表中的数据INSERT 到表products中
MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category);
2、带条件的insert 和update
----update带有where子句 MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name WHERE p.category = np.category; ----insert 和update都带有where子句 MERGE INTO products p USING newproducts np ON (p.product_id = np.product_id) WHEN MATCHED THEN UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.category = ‘DVD‘ WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category != ‘BOOKS‘
3、在ON条件中可以使用常量来insert
MERGE INTO products p USING newproducts np ON (1=0) WHEN NOT MATCHED THEN INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = ‘BOOKS‘
4、update后面可以带delete 子句
merge into products p using newproducts np on(p.product_id = np.product_id) when matched then update set p.product_name = np.product_name delete where category = ‘macle1_cate‘; --查看结果 select * from products; PRODUCT_ID PRODUCT_NAME CATEGORY --------------------------------------- -------------------- -------------------- 1502 macle22 macle2_cate 1503 macle3 macle2_cate 1504 macle macle1_cate 1505 macle5 macle5_cate
注意：1504 中的macle1_cate 满足delete where，但是不满足 on 中的条件，所以没有被删除。
10G中的BUG
当涉及远程dblink时，会报ORA-02064: distributed operation not supported
原因：这是oracle 10g的一个bug，bug ID为4311273
解决方法：不要只写when matched then，还要写when not matched then
例如：
例如: MERGE INTO TB_DW_PUS_BUYER_MST a USING (SELECT * FROM BYR_MST@tdevdb) t ON (a.byr_ctry = t.byr_ctry AND a.byr_code = t.byr_code) WHEN MATCHED THEN UPDATE SET a.BYR_STS = t.BYR_STS WHEN NOT MATCHED THEN INSERT (a.byr_sts) VALUES (t.byr_sts);
原文：https://www.cnblogs.com/marxist/p/12149587.html