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;