程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
 程式師世界 >> 數據庫知識 >> DB2數據庫 >> DB2教程 >> DB2 Merge語句的用法

DB2 Merge語句的用法

編輯:DB2教程

DB2 Merge語句是經常可以用到的DB2語句,下面對DB2 Merge語句作了詳細的介紹,如果您對此方面感興趣的話,不妨一看。

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); 
---雇員表(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   
); 
--更新存在的
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); 
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)的薪水的時候更新,否則不更新,怎麼辦呢?如下:

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

不仔細的朋友可能沒有看出上面兩條語句的區別,哈哈,請仔細對比一下這兩條語句。上面的語句中多了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; 
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; 
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;

以上簡單介紹了Merge語句的使用,它的應用不只是上面介紹的情況,其實它可以應用在很多其他語句不好處理情況,這需要你去發現,記住熟能生巧

  1. 上一頁:
  2. 下一頁:
Copyright © 程式師世界 All Rights Reserved