DB2 Merge語(yǔ)句是經(jīng)?？梢杂玫降腄B2語(yǔ)句，下面對(duì)DB2 Merge語(yǔ)句作了詳細(xì)的介紹，如果您對(duì)此方面感興趣的話(huà)，不妨一看。

DB2 Merge語(yǔ)句的作用非常強(qiáng)大，它可以將一個(gè)表中的數(shù)據(jù)合并到另一個(gè)表中，在合并的同時(shí)可以進(jìn)行插入、刪除、更新等操作。我們還是先來(lái)看個(gè)簡(jiǎn)單的例子吧，假設(shè)你定義了一個(gè)雇員表（employe），一個(gè)經(jīng)理表（manager），如下所示：

---雇員表（EMPLOYE）  
CREATE TABLE EMPLOYE (  
EMPLOYEID INTEGER NOT NULL,---員工號(hào)  
NAME VARCHAR(20) NOT NULL,---姓名  
SALARY DOUBLE---薪水  
);  
INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES   
(1,'張三',1000),  
(2,'李四',2000),  
(3,'王五',3000),  
(4,'趙六',4000),  
(5,'高七',5000);  
--經(jīng)理表（MANAGER）  
CREATE TABLE MANAGER (  
EMPLOYEID INTEGER NOT NULL,---經(jīng)理號(hào)  
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,---員工號(hào)
NAME VARCHAR(20) NOT NULL,---姓名
SALARY DOUBLE---薪水
);
INSERT INTO EMPLOYE (EMPLOYEID,NAME,SALARY) VALUES
(1,'張三',1000),
(2,'李四',2000),
(3,'王五',3000),
(4,'趙六',4000),
(5,'高七',5000);
--經(jīng)理表（MANAGER）
CREATE TABLE MANAGER (
EMPLOYEID INTEGER NOT NULL,---經(jīng)理號(hào)
NAME VARCHAR(20) NOT NULL,---姓名
SALARY DOUBLE---薪水
);
INSERT INTO MANAGER (MANAGERID,NAME,SALARY) VALUES
(3,'王五',5000),
(4,'趙六',6000);

經(jīng)過(guò)一段時(shí)間，你發(fā)現(xiàn)這樣的數(shù)據(jù)模型，或者說(shuō)表結(jié)構(gòu)設(shè)計(jì)簡(jiǎn)直就是一大敗筆，經(jīng)理和雇員都是員工嘛，為什么要設(shè)計(jì)兩個(gè)表呢？發(fā)現(xiàn)錯(cuò)誤后就需要改正，所以你決定，刪除經(jīng)理表（MANAGER）表，將MANAGER 表中的數(shù)據(jù)合并到EMPLOYE 表中，仔細(xì)分析發(fā)現(xiàn)，王五在兩個(gè)表中都存在（可能是干的好升官了），而劉八在EMPLOYE 表中并不存在，現(xiàn)在，我們要求把EMPLOYE 表中不存在的MANAGER都插入到EMPLOYE 表中，存在的更新薪水。該怎么辦呢？這個(gè)問(wèn)題并不難，通常，我們可以分兩步，如下所示：

--更新存在的  
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
);

上面的處理是可以的，但是我們還可以有更簡(jiǎn)單的方法，就是用Merge語(yǔ)句，如下所示：

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

在上面的處理中，我們用經(jīng)理表（MANAGER）的薪水更新了雇員表（EMPLOYE）的薪水，假設(shè)現(xiàn)在要求，如果經(jīng)理表（MANAGER）的薪水>雇員表（EMPLOYE）的薪水的時(shí)候更新，否則不更新，怎么辦呢？如下：

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

不仔細(xì)的朋友可能沒(méi)有看出上面兩條語(yǔ)句的區(qū)別，哈哈，請(qǐng)仔細(xì)對(duì)比一下這兩條語(yǔ)句。上面的語(yǔ)句中多了ELSE IGNORE語(yǔ)句，它的意思正如它英文的意思，其它情況忽略不處理。如果你認(rèn)為理論上應(yīng)該不存在EM.SALARY>MA.SALARY的數(shù)據(jù)，如果有，說(shuō)明有問(wèn)題，你想拋個(gè)異常，怎么辦？如下：

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;

對(duì)于EM.SALARY>MA.SALARY的情況，如果你不想拋異常，而是刪除EMPLOYE中的數(shù)據(jù)，怎么辦？如下：

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;

以上簡(jiǎn)單介紹了Merge語(yǔ)句的使用，它的應(yīng)用不只是上面介紹的情況，其實(shí)它可以應(yīng)用在很多其他語(yǔ)句不好處理情況，這需要你去發(fā)現(xiàn)，記住熟能生巧

【編輯推薦】

DB2游標(biāo)原理介紹

db2日志保存的兩種方式

幾種常用的DB2循環(huán)用法

四種常用的DB2循環(huán)語(yǔ)句

DB2創(chuàng)建數(shù)據(jù)庫(kù)的實(shí)現(xiàn)