SQL语句——14、merge into

该命令使用一条语句从一个或者多个数据源中完成对表的更新和插入数据。ORACLE 9i中，使用此命令必须同时指定UPDATE 和INSERT 关键词,ORACLE 10g 做了如下改动。

1. INSERT 和UPDATE 是可选的
2. UPDATE 和INSERT 后面可以跟WHERE 子句
3. 在ON条件中可以使用常量来INSERT 所有的行到目标表中，不需要连接到源表和目标表
4. UPDATE 子句后面可以跟delete 来去除一些不需要的行。

举例：

准备数据：

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);