db2 merge into语法

MERGE INTO table_name alias1 USING (table|view|sub_query) alias2 ON (join condition) WHEN MATCHED THEN      UPDATE table_name SET col1 = col_val1,col2 = col2_val WHEN NOT MATCHED THEN      INSERT (column_list) VALUES (column_values)   关键字、参数 into子句  在into子句中指定所要修改或者插入数据的目标表 using子句  在using子句中指定用来修改或者插入的数据源。数据源可以是表、视图或者一个子查询语句。 on子句  在on子句中指定执行插入或者修改的满足条件。 when matched | not matched  用该子句通知 数据库如何对满足或不满足条件的结果做出相应的操作。可以使用以下的两类子句。

  merge_update子句  merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。 merge_update子句  merge_update子句执行对目标表中的字段值修改。当在符合on子句条件的情况下执行。如果修改子句执行，则目标表上的修改触发器将被触发。

  限制：当修改一个视图时，不能指定一个default值 merge_insert 子句  merge_insert子句执行当不符合on子句条件时，往目标表中插入数据。如果插入子句执行，则目标表上插入触发器将被触发。 限制：当修改一个视图时，不能指定一个default值

MERGE INTO BONUSES D  USING (SELECT EMPLOYEE_ID, SALARY, DEPARTMENT_ID FROM EMPLOYEES WHERE DEPARTMENT_ID = 80) S  ON (D.EMPLOYEE_ID = S.EMPLOYEE_ID)  WHEN MATCHED THEN UPDATE SET D.BONUS = D.BONUS + S.SALARY*.01  WHEN NOT MATCHED THEN INSERT (D.EMPLOYEE_ID, D.BONUS)  VALUES (S.EMPLOYEE_ID, S.SALARY*0.01);   MERGE INTO XCMDTRANSFERSTATE AS T   USING TABLE (VALUES(?,?,?,?,?,?))  T1(DATASNO,EXTERIORSYSTEM,STATUS,DATA_TIME,MARKFORDELETE,DATATRANSFERNO)   ON (T.DATATRANSFERNO = T1.DATATRANSFERNO)   WHEN MATCHED THEN  update set T.DATASNO = T1.DATASNO,T.EXTERIORSYSTEM = T1.EXTERIORSYSTEM,T.STATUS = T1.STATUS,T.LASTUP_TIME = T1.DATA_TIME,T.MARKFORDELETE = T1.MARKFORDELETE   WHEN NOT MATCHED THEN    INSERT (DATASNO,EXTERIORSYSTEM,STATUS,CREATE_TIME,MARKFORDELETE,DATATRANSFERNO) VALUES (T1.DATASNO,T1.EXTERIORSYSTEM,T1.STATUS,T1.DATA_TIME,T1.MARKFORDELETE,T1.DATATRANSFERNO   MERGE INTO EMPLOYE AS EM   USING MANAGER AS MA   ON EM.EMPLOYEID=MA.MANAGERID   WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY   WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY>MA.SALARY' WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)   ELSE IGNORE; -----

DB2 Merge 语句的作用非常强大，它可以将一个表中的数据合并到另一个表中，在合并的同时可以进行插入、删除、更新等操作。我们还是先来看个简单的例子吧，假设你定义了一个雇员表（employe），一个经理表（manager），如下所示：

---雇员表（EMPLOYE）  

CREATE TABLE EMPLOYE (  

EMPLOYEID INTEGER NOT NULL,---员工号  

NAME VARCHAR(20) NOT NULL,---姓名  

SALARY DOUBLE---薪水  

);  

INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES   

(1,'张三',1000),  

(2,'李四',2000),  

(3,'王五',3000),  

(4,'赵六',4000),  

(5,'高七',5000);  

--经理表（MANAGER）  

CREATE TABLE MANAGER (  

EMPLOYEID INTEGER NOT NULL,---经理号  

NAME VARCHAR(20) NOT NULL,---姓名  

SALARY DOUBLE---薪水  

);  

INSERT INTO MANAGER (MANAGERID,NAME,SALARY) VALUES   

(3,'王五',5000),  

(4,'赵六',6000);

经过一段时间，你发现这样的数据模型，或者说表结构设计简直就是一大败笔，经理和雇员都是员工嘛，为什么要设计两个表呢？发现错误后就需要改正，所以你决定，删除经理表（MANAGER）表，将MANAGER 表中的数据合并到EMPLOYE 表中，仔细分析发现，王五在两个表中都存在（可能是干的好升官了），而刘八在EMPLOYE 表中并不存在，现在，我们要求把EMPLOYE 表中不存在的MANAGER都插入到EMPLOYE 表中，存在的更新薪水。该怎么办呢？这个问题并不难，通常，我们可以分两步，如下所示：

--更新存在的  

UPDATE EMPLOYE AS EM SET SALARY=(SELECT SALARY FROM MANAGER WHERE MANAGERID=EM.EMPLOYEID)  

WHERE EMPLOYEID IN (  

SELECT MANAGERID FROM MANAGER  

);  

---插入不存在的  

INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY)  

SELECT MANAGERID,NAME,SALARY FROM MANAGER WHERE MANAGERID NOT IN (  

SELECT EMPLOYEID FROM EMPLOYE  

);

上面的处理是可以的，但是我们还可以有更简单的方法，就是用Merge语句，如下所示：

MERGE INTO EMPLOYE AS EM  

USING MANAGER AS MA  

ON EM.EMPLOYEID=MA.MANAGERID  

WHEN MATCHED THEN UPDATE SET EM.SALARY=MA.SALARY  

WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);

在上面的处理中，我们用经理表（MANAGER）的薪水更新了雇员表（EMPLOYE）的薪水，假设现在要求，如果经理表（MANAGER）的薪水>雇员表（EMPLOYE）的薪水的时候更新，否则不更新，怎么办呢？如下：

-1

MERGE INTO EMPLOYE AS EM  

USING MANAGER AS MA  

ON EM.EMPLOYEID=MA.MANAGERID  

WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY  

WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY);

-2

USING MANAGER AS MA

ON EM.EMPLOYEID=MA.MANAGERID

WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY

WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)

else ignore

不仔细的朋友可能没有看出上面两条语句的区别，哈哈，请仔细对比一下这两条语句。上面的语句中多了ELSE IGNORE语句，它的意思正如它英文的意思，其它情况忽略不处理。

如果你认为理论上应该不存在EM.SALARY>MA.SALARY的数据，如果有，说明有问题，你想抛个异常，怎么办？如下：

MERGE INTO EMPLOYE AS EM  

USING MANAGER AS MA  

ON EM.EMPLOYEID=MA.MANAGERID  

WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY  

WHEN MATCHED AND EM.SALARY>MA.SALARY THEN SIGNAL SQLSTATE '70001' SET MESSAGE_TEXT = 'EM.SALARY>MA.SALARY'

WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)  

ELSE IGNORE;

对于EM.SALARY>MA.SALARY的情况，如果你不想抛异常，而是删除EMPLOYE中的数据，怎么办？如下：

MERGE INTO EMPLOYE AS EM  

USING MANAGER AS MA  

ON EM.EMPLOYEID=MA.MANAGERID  

WHEN MATCHED AND EM.SALARY<MA.SALARY THEN UPDATE SET EM.SALARY=MA.SALARY  

WHEN MATCHED AND EM.SALARY>MA.SALARY THEN DELETE  

WHEN NOT MATCHED THEN INSERT VALUES (MA.MANAGERID,MA.NAME,MA.SALARY)  

ELSE IGNORE;