MERGE 语句示例说明

 

IBM Informix IDS merge语句为DML语句，用来对比两个表的记录情况，根据情况执行insert的同时执行update或者delete操作。

IDS11.5XC6及以上版本支持该功能

1.表sale为目标表，new_sale为来源表，通过cust_id对比两个表的数据，当sale表有与new_sale相同的记录时，执行update更新sale表的salecount属性，当sale表中没有new_sale中相应的记录，则把该记录insert 到sale表中

 

MERGE INTO sale USING new_sale AS n
ON sale.cust_id = n.cust_id
WHEN MATCHED THEN UPDATE
SET sale.salecount = sale.salecount + n.salecount
WHEN NOT MATCHED THEN INSERT (cust_id, salecount)
VALUES (n.cust_id, n.salecount);

 

---

2.

 

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num
WHEN MATCHED THEN
UPDATE SET c.fname = e.fname,

c.lname = e.lname,
c.company = e.company,
c.address1 = e.address1,
c.address2 = e.address2,
c.city = e.city,
c.state = e.state,
c.zipcode = e.zipcode,
c.phone = e.phone
WHEN NOT MATCHED THEN
INSERT (c.fname, c.lname, c.company, c.address1, c.address2,
c.city, c.state, c.zipcode, c.phone)
VALUES
(e.fname, e.lname, e.company, e.address1, e.address2,
e.city, e.state, e.zipcode, e.phone);

 

3.

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num
AND c.fname=e.fname AND c.lname=e.lname
WHEN MATCHED THEN
UPDATE SET c.fname = e.fname,
c.lname = e.lname,
c.company = e.company,
c.address1 = e.address1,
c.address2 = e.address2,
c.city = e.city,
c.state = e.state,
c.zipcode = e.zipcode,
c.phone = e.phone
WHEN NOT MATCHED THEN
INSERT
(c.fname, c.lname, c.company, c.address1, c.address2,
c.city, c.state, c.zipcode, c.phone)
VALUES
(e.fname, e.lname, e.company, e.address1, e.address2,
e.city, e.state, e.zipcode, e.phone);

 

4.

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num
WHEN MATCHED THEN
UPDATE SET c.fname = e.fname,
c.lname = e.lname,
c.company = e.company,
c.address1 = e.address1,
c.address2 = e.address2,
c.city = e.city,
c.state = e.state,
c.zipcode = e.zipcode,
c.phone = e.phone ;

 

5.

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num
WHEN MATCHED THEN
DELETE ;

 

6.

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num AND c.fname=e.fname
AND c.lname=e.lname
WHEN NOT MATCHED THEN
INSERT
(c.fname, c.lname, c.company, c.address1, c.address2,
c.city, c.state, c.zipcode, c.phone)
VALUES
(e.fname, e.lname, e.company, e.address1, e.address2,
e.city, e.state, e.zipcode, e.phone);

 

7.

MERGE INTO customer c
USING ext_customer e
ON c.customer_num=e.customer_num AND c.fname=e.fname AND c.lname=e.lname
WHEN NOT MATCHED THEN
INSERT
(c.fname, c.lname, c.company, c.address1, c.address2,
c.city, c.state, c.zipcode, c.phone)
VALUES
(e.fname, e.lname, e.company, e.address1, e.address2,
e.city, e.state, e.zipcode, e.phone)
WHEN MATCHED THEN UPDATE
SET c.fname = e.fname,
c.lname = e.lname,
c.company = e.company,
c.address1 = e.address1,
c.address2 = e.address2,
c.city = e.city,
c.state = e.state,
c.zipcode = e.zipcode,
c.phone = e.phone ;

 

8.

MERGE INTO customer c
USING (SELECT * from ext_customer e1, orders e2
WHERE e1.customer_num=e2.customer_num ) e
ON c.customer_num=e.customer_num AND c.fname=e.fname
AND c.lname=e.lname
WHEN NOT MATCHED THEN
INSERT (c.fname, c.lname, c.company, c.address1, c.address2,
c.city, c.state, c.zipcode, c.phone)
VALUES (e.fname, e.lname, e.company, e.address1, e.address2,
e.city, e.state, e.zipcode, e.phone)
WHEN MATCHED THEN
UPDATE SET c.fname = e.fname,
c.lname = e.lname,
c.company = e.company,
c.address1 = e.address1,
c.address2 = e.address2,
c.city = e.city,
c.state = e.state,
c.zipcode = e.zipcode,
c.phone = e.phone ;