SQLServerのMERGEでINSERTとUPDATEを1行で実行する

あるテーブルのデータを更新する際には「該当行があればUPDATE、無ければINSERT」のようにデータが存在するかどうかでIF文を書いてINSERTとUPDATEを切り分ける必要がある。

MERGE文を利用するとわざわざIF文を書く必要がなくなるので便利です。

MERGE文を使うと行の存在有無を気にせずに1行のSQLでOK

そんな面倒を解決するためにSQLServer 2008からはMERGE文が利用できるようになりました。

これを利用すると、指定した条件に応じて挿入(INSERT)、更新(UPDATE)、削除(DELETE)を実行することが可能になります。

MERGEステートメントの構成

MERGEステートメントは、次の主要な句で構成されています。

MERGE句	挿入(INSERT)、更新(UPDATE)、削除(DELETE)の各操作の対象となるテーブルまたはビューを指定します。
USING句	対象と結合されるデータソースを指定します。
ON句	対象とソースが一致しているかどうか判断する結合条件を指定します。
WHEN句	WHEN MATCHED、WHEN NOT MATCHED BY TARGET、および WHEN NOT MATCHED BY SOURCE は、ON句の結果、および WHEN 句で指定した追加の検索条件の結果に基づいて実行する操作を指定します。
OUTPUT句	挿入(INSERT)、更新(UPDATE)、削除(DELETE)される対象の行ごとに 1 行を返します。

1つのテーブルに対するMERGE文

以下は1つのテーブルに対してのMERGE文で、該当行があればUPDATE（更新）、なければINSERT（挿入）します。

UPDATE（更新）の例

以下はデータが存在した場合のUPDATE（更新）の例です。

CREATE TABLE test_table
(
    no       INT
    ,name    VARCHAR(20)
    ,age     INT
)
 
INSERT INTO test_table VALUES( 10,'次郎さん',40)
 
MERGE INTO test_table AS A
    USING (SELECT 10 AS no,'太郎さん' AS name, 30 AS age ) AS B
    ON
    (
       A.no = B.no
    )
    WHEN MATCHED THEN
        UPDATE SET
             name = B.name
            ,age = B.age
    WHEN NOT MATCHED THEN
        INSERT (no,name,age)
        VALUES
        (
             B.no
            ,B.name
            ,B.age
        )
;
 
SELECT no, name, age FROM test_table ORDER BY no
 
DROP TABLE test_table

上記の実行結果は

no    name     age
10   太郎さん   30

になります。

最初に検証用に挿入した「nameが二郎さん,ageが40」のデータが「nameが太郎さん,ageが30」に更新されています。

ON以下の「A.no=B.no」の条件が一致したのでUPDATE（更新）されています。

INSERT（挿入）の例

以下はデータが存在しない場合のINSERT（挿入）の例です。

CREATE TABLE test_table
(
    no       INT
    ,name    VARCHAR(20)
    ,age     INT
)
 
INSERT INTO test_table VALUES( 20,'次郎さん',40)
 
MERGE INTO test_table AS A
    USING (SELECT 10 AS no,'太郎さん' AS name, 30 AS age ) AS B
    ON
    (
       A.no = B.no
    )
    WHEN MATCHED THEN
        UPDATE SET
             name = B.name
            ,age = B.age
    WHEN NOT MATCHED THEN
        INSERT (no,name,age)
        VALUES
        (
             B.no
            ,B.name
            ,B.age
        )
;
 
SELECT no, name, age FROM test_table ORDER BY no
 
DROP TABLE test_table

上記の実行結果は

no    name    age
10   太郎さん  30
20   次郎さん  40

になります。

ON以下の「A.no=B.no」の条件が一致しなかったのでINSERT（挿入）されています。

2つのテーブルから結果をMERGEするMERGE文

以下は2つのテーブルに対してのMERGE文で、test_tableAにtest_tableBの該当行があればUPDATE（更新）、なければINSERT（挿入）します。

UPDATE（更新）の例

以下はデータが存在した場合のUPDATE（更新）の例です。

CREATE TABLE test_tableA
(
    no       INT
    ,name    VARCHAR(20)
    ,age     INT
)
CREATE TABLE test_tableB
(
    no       INT
    ,name    VARCHAR(20)
    ,age     INT
)
 
INSERT INTO test_tableA VALUES( 10,'太郎さんA',100)
INSERT INTO test_tableB VALUES( 10,'太郎さんB',30)
 
MERGE INTO test_tableA AS A
    USING test_tableB AS B
    ON
    (
       A.no = B.no
    )
    WHEN MATCHED THEN
        UPDATE SET
             name = B.name
            ,age = B.age
    WHEN NOT MATCHED THEN
        INSERT (no,name,age)
        VALUES
        (
             B.no
            ,B.name
            ,B.age
        )
;
 
SELECT no, name, age FROM test_tableA ORDER BY no
 
DROP TABLE test_tableA
DROP TABLE test_tableB

上記の実行結果は

no    name    age
10  太郎さんB  30

になります。

test_tableAにもtest_tableBにも「no=10」の太郎さんが存在したのでtest_tableBの内容でtest_tableAの太郎さんが更新されています。

INSERT（挿入）の例

以下はデータが存在した場合のINSERT（挿入）の例です。

CREATE TABLE test_tableA
(
    no       INT
    ,name    VARCHAR(20)
    ,age     INT
)
CREATE TABLE test_tableB
(
    no       INT
    ,name    VARCHAR(20)
    ,age     INT
)
 
INSERT INTO test_tableB VALUES( 10,'太郎さんB',30)
 
MERGE INTO test_tableA AS A
    USING test_tableB AS B
    ON
    (
       A.no = B.no
    )
    WHEN MATCHED THEN
        UPDATE SET
             name = B.name
            ,age = B.age
    WHEN NOT MATCHED THEN
        INSERT (no,name,age)
        VALUES
        (
             B.no
            ,B.name
            ,B.age
        )
;
 
SELECT no, name, age FROM test_tableA ORDER BY no
 
DROP TABLE test_tableA
DROP TABLE test_tableB

上記の実行結果は

no    name    age
10  太郎さんB  30

になります。

test_tableAには「no=10」の太郎さんが存在しなかったのでtest_tableBの内容でtest_tableAに太郎さんが挿入されています。

このようにMERGE文を利用するればIF文で該当データの存在確認をせずにUPDATE（更新）したりINSERT（挿入）したりできます。

また、UPDATEの代わりにDELETEを利用すればデータの削除も行えます。

PostgreSQLでMERGE文を実行方法

PostgreSQLでINSERTとUPDATEを1行で実行するMERGE（UPSERT）を行う

PostgreSQLでは SQLServerのMERGE 文と同じことを行うにはCONFLICTを使って行います。