[Sql Server] MERGE語法教學

MERGE語句是Oracle9i新增的語法，用來合併UPDATE和INSERT語句。
通過MERGE語句，根據一張表或子查詢的連接條件對另外一張表進行查詢，
連接條件匹配上的進行UPDATE，無法匹配的執行INSERT。
這個語法僅需要一次全表掃描就完成了全部工作，執行效率要高於INSERT＋UPDATE。

PS. 也適用於Microsoft Sql Server 2008以上版本

範例:

兩個表格JB_BUYLIST_T(購物清單), JB_LSTBUY_D(紀錄最後一次買的東西)

我們現在要做的事情是…

抓出每個人購物清單中最後一次買的東西，然後把它更新或新增到JB_LSTBUY_D這邊。

Step1.建立表格

/*購物清單*/

CREATE TABLE JB_BUYLIST_T

(

ID       VARCHAR(10),

ITEM     VARCHAR(20),

BUY_TIME VARCHAR(8)

);

/*紀錄最後一次買的東西*/

CREATE TABLE JB_LSTBUY_D

(

ID           VARCHAR(10),

LST_BUY_ITEM VARCHAR(20)

);

Step2.先新增三筆購物清單

INSERT INTO JB_BUYLIST_T VALUES('JB','雜誌','20110701');

INSERT INTO JB_BUYLIST_T VALUES('JB','飯糰','20110703');

INSERT INTO JB_BUYLIST_T VALUES('D12','便當','20110702');

Step3. 執行MERGE語法

MERGE INTO JB_LSTBUY_D a USING

(

           SELECT ID,ITEM

           FROM

           (

                     SELECT

                     RANK() OVER (PARTITION BY ID ORDER BY BUY_TIME DESC) as RNK,

                     ID,

                     ITEM

                     FROM JB_BUYLIST_T

           ) X 

WHERE RNK=1

) b

ON

(

a.ID = b.ID -- AND ... 如果還有其它條件

)

WHEN MATCHED THEN UPDATE SET a.LST_BUY_ITEM = b.ITEM

WHEN NOT MATCHED THEN INSERT VALUES (b.ID, b.ITEM);

PS. b裡面是利用RANK()挑出每人最後一筆購物品。

   藍色→目標表格

   綠色→來源表格

   紅色→固定寫法

結果成功新增兩筆資料到 JB_LSTBUY_D :

Step4. 我們接下來試看看能不能同時做更新和新增…

INSERT INTO JB_BUYLIST_T VALUES('JB','飲料','20110705');

INSERT INTO JB_BUYLIST_T VALUES('D12','飲料','20110705');

INSERT INTO JB_BUYLIST_T VALUES('YOYO','飲料','20110705');

目前清單如下圖

Step5. 再執行步驟3的Merge語法，結果如下 (成功更新原本的兩筆資料 和 新增一筆資料):