merge into在mysql中的替代实现

merge into语法

merge语法：
MERGE INTO [target-table] A USING [source-table sql] B 
ON([conditional expression] and [...]...) 
WHEN MATCHED THEN
 [UPDATE sql] 
WHEN NOT MATCHED THEN 
[INSERT sql]

初始化数据

#实例：–oracle与mysql数据初始化

create table PRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR(30),
    CATEGORY VARCHAR(30)
    );
create table NEWPRODUCTS
    (
    PRODUCT_ID INTEGER,
    PRODUCT_NAME VARCHAR(30),
    CATEGORY VARCHAR(30)
    );
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');
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;

查询创建的表
SQL> select * from products;
SQL> select * from newproducts;

merge into实现：

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
WHEN NOT MATCHED THEN
INSERT
VALUES (np.product_id, np.product_name, np.category);

SQL> select * from products;

mysql实现merge into功能有：

1、replace into

需要注意的是该语句使用的前提是 旧表和新表的product_id是主键或者unique类型否则会重复插入.
为了做这个功能的测试，给两张表分表添加主键约束。

Replace INTO products SELECT * FROM newproducts; 

2、insert+update

insert into PRODUCTS(PRODUCT_ID,PRODUCT_NAME,CATEGORY)
select np.PRODUCT_ID,np.PRODUCT_NAME,np.CATEGORY from PRODUCTS p
right join NEWPRODUCTS np
on p.PRODUCT_ID=np.PRODUCT_ID
where p.PRODUCT_ID is null;

update PRODUCTS p,NEWPRODUCTS np
SET p.PRODUCT_NAME=np.PRODUCT_NAME,
p.CATEGORY=np.CATEGORY
where p.PRODUCT_ID=np.PRODUCT_ID;