【SQL/SQLServer】MERGE文(UPSERT)

SQLServer2008以降では、MERGE文と言うものを利用することができる。

MERGE文を用いると

テーブルにデータがある場合にはUPDATE
データがない場合にはINSERT

といったように1文でデータの更新処理等をまとめたりすることができる。

MERGE (Transact-SQL)

前提

試した環境

Microsoft SQL Server 2014 Express
SQL Server 2014 Management Studio Express

記事で使用するテーブルは下記

CREATE TABLE test
(
    No       INT
    ,Name    VARCHAR(20)
)

データを下記のように追加しておく。

f:id:beatdjam:20150306003810p:plain

UPDATEとINSERTを同時に行う例

各構文の解説
MERGE INTO	[操作対象のテーブル] AS [テーブルの代替名]
USING	[比較対象のデータ] AS [テーブルの代替名]
ON	[比較条件]
WHEN MATCHED THEN	比較条件と一致しているレコードへの操作内容
WHEN NOT MATCHED THEN (または) WHEN NOT MATCHED BY TARGET THEN	操作対象に存在しないレコードへの操作内容
WHEN NOT MATCHED BY SOURCE THEN	比較対象に存在しないレコードへの操作内容

※WHEN ～ THENは、WHEN MATCHED AND [条件句] THENのように、条件を付加して書ける、らしい。

以下では全てリテラル値を用いているが、USINGはテーブル等もデータソースとして用いることができる。
名前の通り、2つのテーブルをマージする際に便利。

1. UPDATEが実行される場合

--MERGE INTO [操作対象のテーブル] AS [テーブルの代替名]
MERGE INTO
    test AS A
    --USING [比較対象のデータ] AS [テーブルの代替名]
USING
    (
        SELECT
            1 AS No,
            'beatdjam' AS Name
    ) AS B
    --ON [比較条件]
ON  (
        A.No = B.No
    )
    --WHEN MATCHED THEN 比較条件と一致しているレコードへの操作内容
WHEN MATCHED THEN
    UPDATE
    SET
        Name = B.Name
        --WHEN NOT MATCHED THEN 　　　比較条件と一致していないレコードへの操作内容
WHEN NOT MATCHED THEN
    INSERT(No, Name)
    VALUES
        (B.No, B.Name)
;

f:id:beatdjam:20150306003644p:plain

実行後にNoが一致したレコードの値が変わっている。

f:id:beatdjam:20150306004001p:plain

2. INSERTが実行される場合

--MERGE INTO [操作対象のテーブル] AS [テーブルの代替名]
MERGE INTO
    test AS A
    --USING [比較対象のデータ] AS [テーブルの代替名]
USING
    (
        SELECT
            3 AS No,
            'beateck!' AS Name
    ) AS B
    --ON [比較条件]
ON  (
        A.No = B.No
    )
    --WHEN MATCHED THEN 比較条件と一致しているレコードへの操作内容
WHEN MATCHED THEN
    UPDATE
    SET
        Name = B.Name
        --WHEN NOT MATCHED THEN 　　　比較条件と一致していないレコードへの操作内容
WHEN NOT MATCHED THEN
    INSERT(No, Name)
    VALUES
        (B.No, B.Name)
;

f:id:beatdjam:20150306005140p:plain

実行後に新しいレコードが増えている。

f:id:beatdjam:20150306005244p:plain

3. MERGE文ではDELETEも実行できる

--MERGE INTO [操作対象のテーブル] AS [テーブルの代替名]
MERGE INTO
    test AS A
    --USING [比較対象のデータ] AS [テーブルの代替名]
USING
    (
        SELECT
            3 AS No,
            'beateck!' AS Name
    ) AS B
    --ON [比較条件]
ON  (
        A.No = B.No
    )
    --WHEN MATCHED THEN 比較条件と一致しているレコードへの操作内容
WHEN MATCHED THEN
    DELETE
    --WHEN NOT MATCHED THEN 　　　比較条件と一致していないレコードへの操作内容
WHEN NOT MATCHED THEN
    INSERT(No, Name)
    VALUES
        (B.No, B.Name)
;