SQLServerのMERGEでINSERT/UPDATE/DELETEを1回のSQLで実行する

あるテーブルやビューに既に対象のレコードが存在している場合はUPDATE（更新）し、存在していない場合はINSERT（挿入）する。
データベースを使ったアプリケーションを開発していると、よく遭遇するケースの処理です。
こんな場合はどのように処理を行えばよいでしょう。

一旦SELECTを実行して対象のレコードを取得して、データの取得有無の判断をIF文で分岐し、レコードが取得できればUPDATEを実行、レコードが取得できなければINSERTを実行する。

単純に考えれば上記のような処理になります。しかし、上記の処理はSELECTという処理と、UPDATEまたはINSERTという処理の2つの処理に分かれており、1回のSQLで実行することはできません。

SQLServerにはある条件を満たすデータが存在している場合はUPDATEやDELETEの処理、データが存在していない場合はINSERTの処理を行う構文が用意されており、1回のSQLで処理を分岐することができます。

今回はそんな便利な処理を可能にするMERGEステートメントについてご紹介します。

1 使用するテーブルとデータの準備
2 MERGEステートメント
3 INSERTが実行される場合の例
4 UPDATEが実行される場合の例
5 MERGEステートメントを使用したテーブル間の更新
6 DELETEが実行される場合の例

使用するテーブルとデータの準備

本記事ではMERGE文での処理のサンプルSQLを記述するために、下記のテーブルを作成します。

テーブル定義

会員テーブル

列名	データ型
会員ID	INT
氏名	NVARCHAR(20)
在籍年数	TINYINT

優待会員テーブル

列名	データ型
従業員ID	INT
優待区分	NVARCHAR(1)

優待区分コード

S: シルバー (在籍年数:1年以上)
G: ゴールド (在籍年数:3年以上)
P: プラチナ (在籍年数:5年以上)

サンプルデータ

会員テーブル

会員ID	氏名	在籍年数
1	会員氏名1	4
2	会員氏名2	1
3	会員氏名3	0
4	会員氏名4	6

優待会員テーブル

従業員ID	優待区分
1	G
4	P

テーブルのCREATEとINSERT

テーブルのCREATE

-- 会員テーブルの作成
CREATE TABLE [会員]
(
	[会員ID] [int] NOT NULL,
	[氏名] [nvarchar](20) COLLATE Japanese_BIN NOT NULL,
	[在籍年数] [tinyint] NOT NULL,
  CONSTRAINT [PK_会員] PRIMARY KEY CLUSTERED 
  (
	[会員ID] ASC
  )
);
-- 優待会員テーブルの作成
CREATE TABLE [優待会員]
(
	[会員ID] [int] NOT NULL,
	[優待区分] [nvarchar](1) COLLATE Japanese_BIN NOT NULL,
  CONSTRAINT [PK_優待会員] PRIMARY KEY CLUSTERED 
  (
	[会員ID] ASC,
	[優待区分] ASC
  )
);

-- 会員テーブルの作成

CREATE TABLE [会員]

(

[会員ID] [int] NOT NULL,

[氏名] [nvarchar](20) COLLATE Japanese_BIN NOT NULL,

[在籍年数] [tinyint] NOT NULL,

CONSTRAINT [PK_会員] PRIMARY KEY CLUSTERED

(

[会員ID] ASC

)

);

-- 優待会員テーブルの作成

CREATE TABLE [優待会員]

(

[会員ID] [int] NOT NULL,

[優待区分] [nvarchar](1) COLLATE Japanese_BIN NOT NULL,

CONSTRAINT [PK_優待会員] PRIMARY KEY CLUSTERED

(

[会員ID] ASC,

[優待区分] ASC

)

);

データのINSERT

-- 会員テーブルのデータを挿入
INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (1, '会員 氏名1' ,4);
INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (2, '会員 氏名2' ,1);
INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (3, '会員 氏名3' ,0);
INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (4, '会員 氏名4' ,6);
-- 優待会員テーブルのデータを挿入
INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (1, 'G');
INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (4, 'P');

-- 会員テーブルのデータを挿入

INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (1, '会員氏名1' ,4);

INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (2, '会員氏名2' ,1);

INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (3, '会員氏名3' ,0);

INSERT INTO [会員] ([会員ID], [氏名], [在籍年数]) VALUES (4, '会員氏名4' ,6);

-- 優待会員テーブルのデータを挿入

INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (1, 'G');

INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (4, 'P');

MERGEステートメント

MERGEステートメントは挿入(INSERT)、更新(UPDATE)、または削除(DELETE)の対象となるテーブルまたはビューと、挿入(INSERT)、更新(UPDATE)、または削除(DELETE)するデータを比較し、レコードが存在する場合の操作と存在しない場合の操作を分岐します。
MERGEステートメントはいくつかの要素で構成されます。
MERGEステートメントの主な構成要素（句）は以下のようになります。

MERGE [INTO]

挿入(INSERT)/更新(UPDATE)/削除(DELETE)操作を行う対象のテーブルまたはビューを指定します。
[操作対象のテーブル(ビュー)] AS [代替名]の形式で記述します。
USING

挿入(INSERT)/更新(UPDATE)/削除(DELETE)操作を行う対象のテーブルまたはビューと比較するデータを指定します。
[比較対象のデータ] AS [代替名]の形式で記述します。
[比較対象のデータ]にはSELECTステートメントを使用します。
ON

操作対象のテーブルまたはビューと比較対象のデータを比較する条件を指定します。
WHEN MATCHED THEN

操作対象のテーブルまたはビューに存在するレコードへの操作内容を指定します。
更新(UPDATE)/削除(DELETE)操作はここに記述することになります。
WHEN NOT MATCHED THEN

操作対象のテーブルまたはビューに存在しないレコードへの操作内容を指定します。
挿入(INSERT)操作はここに記述することになります。
「WHEN NOT MATCHED THEN」は「WHEN NOT MATCHED BY TARGET THEN」と記述することもできます。
WHEN NOT MATCHED BY SOURCE THEN

比較対象のデータに存在しないレコードへの操作内容を指定します。
削除(DELETE)操作はここに記述することになります。

INSERTが実行される場合の例

WHEN NOT MATCHED THEN内のINSERTステートメントが実行される場合の例を以下に示します。

-- 会員テーブルに存在しないレコードを挿入する例
MERGE INTO
    -- 操作対象のテーブル
    [会員]
USING
    -- 比較対象のデータ
    (
    SELECT
        5 AS [会員ID],
        '会員 太郎' AS [氏名],
        0 AS [在籍年数]
    ) AS [新規会員]
ON
    -- 比較条件
    (
    [会員].[会員ID] = [新規会員].[会員ID]
    )
WHEN MATCHED THEN
    -- 比較条件が一致するレコードへの操作（処理）
    UPDATE SET
        [氏名] = [新規会員].[氏名],
        [在籍年数] = [新規会員].[在籍年数]
WHEN NOT MATCHED THEN
    -- 比較条件が一致いないレコードへの操作（処理）
    INSERT
    (
        [会員ID],
        [氏名],
        [在籍年数]
    )
    VALUES
    (
        [新規会員].[会員ID],
        [新規会員].[氏名],
        [新規会員].[在籍年数]
    )
;

-- 会員テーブルに存在しないレコードを挿入する例

MERGE INTO

-- 操作対象のテーブル

[会員]

USING

-- 比較対象のデータ

(

SELECT

5 AS [会員ID],

'会員太郎' AS [氏名],

0 AS [在籍年数]

) AS [新規会員]

-- 比較条件

(

[会員].[会員ID] = [新規会員].[会員ID]

)

WHEN MATCHED THEN

-- 比較条件が一致するレコードへの操作（処理）

UPDATE SET

[氏名] = [新規会員].[氏名],

[在籍年数] = [新規会員].[在籍年数]

WHEN NOT MATCHED THEN

-- 比較条件が一致いないレコードへの操作（処理）

INSERT

(

[会員ID],

[氏名],

[在籍年数]

)

VALUES

(

[新規会員].[会員ID],

[新規会員].[氏名],

[新規会員].[在籍年数]

)

;

上記のSQLでは会員IDが5のレコードをMERGEステートメントを使用して処理しています。
会員IDが5のレコードは会員テーブルに存在しないので、ON句の[会員].[会員ID] = [新規会員].[会員ID]の条件が一致せず、WHEN NOT MATCHED THEN内の操作が実行され、レコードが挿入(INSERT)されます。

-- 会員テーブルをSELECT
SELECT * FROM [会員] ORDER BY [会員ID];

1 2	-- 会員テーブルをSELECT SELECT * FROM [会員] ORDER BY [会員ID];

実行結果

UPDATEが実行される場合の例

WHEN MATCHED THEN内のINSERTステートメントが実行される場合の例を以下に示します。

-- 会員テーブルに存在するレコードを更新する例
MERGE INTO
    -- 操作対象のテーブル
    [会員]
USING
    -- 比較対象のデータ
    (
    SELECT
        2 AS [会員ID],
        '会員 花子' AS [氏名],
        2 AS [在籍年数]
    ) AS [新規会員]
ON
    -- 比較条件
    (
    [会員].[会員ID] = [新規会員].[会員ID]
    )
WHEN MATCHED THEN
    -- 比較条件が一致するレコードへの操作（処理）
    UPDATE SET
        [氏名] = [新規会員].[氏名],
        [在籍年数] = [新規会員].[在籍年数]
WHEN NOT MATCHED THEN
    -- 比較条件が一致いないレコードへの操作（処理）
    INSERT
    (
        [会員ID],
        [氏名],
        [在籍年数]
    )
    VALUES
    (
        [新規会員].[会員ID],
        [新規会員].[氏名],
        [新規会員].[在籍年数]
    )
;

-- 会員テーブルに存在するレコードを更新する例

MERGE INTO

-- 操作対象のテーブル

[会員]

USING

-- 比較対象のデータ

(

SELECT

2 AS [会員ID],

'会員花子' AS [氏名],

2 AS [在籍年数]

) AS [新規会員]

-- 比較条件

(

[会員].[会員ID] = [新規会員].[会員ID]

)

WHEN MATCHED THEN

-- 比較条件が一致するレコードへの操作（処理）

UPDATE SET

[氏名] = [新規会員].[氏名],

[在籍年数] = [新規会員].[在籍年数]

WHEN NOT MATCHED THEN

-- 比較条件が一致いないレコードへの操作（処理）

INSERT

(

[会員ID],

[氏名],

[在籍年数]

)

VALUES

(

[新規会員].[会員ID],

[新規会員].[氏名],

[新規会員].[在籍年数]

)

;

上記のSQLでは会員IDが2のレコードをMERGEステートメントを使用して処理しています。
会員IDが2のレコードは会員テーブルに存在するので、ON句の[会員].[会員ID] = [新規会員].[会員ID]の条件が一致し、WHEN MATCHED THEN内の操作が実行され、レコードが更新(UPDATE)されます。

-- 会員テーブルをSELECT
SELECT * FROM [会員] ORDER BY [会員ID];

1 2	-- 会員テーブルをSELECT SELECT * FROM [会員] ORDER BY [会員ID];

実行結果

ここまでの例ではUSING句の比較対象のデータをリテラルで記載してきましたが、テーブルやビューとの間での更新処理も可能です。
テーブルやビューとの間で処理を行う場合はSELECTステートメントにテーブルやビューを記述します。

MERGEステートメントを使用したテーブル間の更新

次の例では操作対象の「優待会員テーブル」に対して、比較対象の「会員テーブル」のレコードを更新します。
「優待会員テーブル」の「会員ID」には「会員テーブル」の「会員ID」を、「優待会員テーブル」の「優待区分」には「会員テーブル」の「在籍年数」が1年以上の場合は「S:シルバー」を、3年以上の場合は「G:ゴールド」を5年以上の場合は「P:プラチナ」を指定しています。

-- 優待会員テーブルに会員テーブルのデータを更新
MERGE INTO
    -- 操作対象のテーブル
    [優待会員]
USING
    -- 比較対象のデータ
    (
    SELECT
        [会員ID],
        [氏名],
        CASE
            WHEN [在籍年数] >= 5 THEN 'P'
            WHEN [在籍年数] >= 3 THEN 'G'
            WHEN [在籍年数] >= 1 THEN 'S'
            ELSE ''
            END AS [優待区分]
    FROM
        [会員]
    ) AS [会員]
ON
    -- 比較条件
    (
    [優待会員].[会員ID] = [会員].[会員ID]
    )
WHEN MATCHED THEN
    -- 比較条件が一致するレコードへの操作（処理）
    UPDATE SET
        [会員ID] = [会員].[会員ID],
        [優待区分] = [会員].[優待区分]
WHEN NOT MATCHED THEN
    -- 比較条件が一致いないレコードへの操作（処理）
    INSERT
    (
        [会員ID],
        [優待区分]
    )
    VALUES
    (
        [会員].[会員ID],
        [会員].[優待区分]
    )
;

-- 優待会員テーブルに会員テーブルのデータを更新

MERGE INTO

-- 操作対象のテーブル

[優待会員]

USING

-- 比較対象のデータ

(

SELECT

[会員ID],

[氏名],

CASE

WHEN [在籍年数] >= 5 THEN 'P'

WHEN [在籍年数] >= 3 THEN 'G'

WHEN [在籍年数] >= 1 THEN 'S'

ELSE ''

END AS [優待区分]

FROM

[会員]

) AS [会員]

-- 比較条件

(

[優待会員].[会員ID] = [会員].[会員ID]

)

WHEN MATCHED THEN

-- 比較条件が一致するレコードへの操作（処理）

UPDATE SET

[会員ID] = [会員].[会員ID],

[優待区分] = [会員].[優待区分]

WHEN NOT MATCHED THEN

-- 比較条件が一致いないレコードへの操作（処理）

INSERT

(

[会員ID],

[優待区分]

)

VALUES

(

[会員].[会員ID],

[会員].[優待区分]

)

;

優待会員テーブルと会員テーブル間のMERGE文で会員IDが「2, 3, 5」の会員のレコードが挿入(INSERT)され「1, 4」の会員のレコードが更新(UPDATE)されます。

-- 優待会員テーブルをSELECT
SELECT * FROM [優待会員] ORDER BY [会員ID];

1 2	-- 優待会員テーブルをSELECT SELECT * FROM [優待会員] ORDER BY [会員ID];

実行結果

上記のSQLでは会員テーブルのすべてのレコードを優待会員テーブルに更新しましたが、特定のレコードのみ更新対象とすることもできます。
次の例では会員テーブルの在籍年数が1年以上の場合のみ優待会員テーブルにデータと更新します。

まず、実行前に優待会員テーブルのデータを一旦元に戻します。

-- 優待会員テーブルのデータを一旦削除
DELETE [優待会員];
-- 優待会員テーブルのデータを挿入
INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (1, 'G');
INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (4, 'P');

-- 優待会員テーブルのデータを一旦削除

DELETE [優待会員];

-- 優待会員テーブルのデータを挿入

INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (1, 'G');

INSERT INTO [優待会員] ([会員ID], [優待区分]) VALUES (4, 'P');

MERGEステートメントで更新します。

-- 優待会員テーブルに特定の会員テーブルのデータを更新
MERGE INTO
    [優待会員]
USING
    (
    SELECT
        [会員ID],
        [氏名],
        CASE
            WHEN [在籍年数] >= 5 THEN 'P'
            WHEN [在籍年数] >= 3 THEN 'G'
            ELSE 'S'
            END AS [優待区分]
    FROM
        [会員]
    WHERE
        -- 会員テーブルの在籍年数が1年以上の会員のみ対象にする
        [会員].[在籍年数] >= 1 
    ) AS [会員]
ON
    (
    [優待会員].[会員ID] = [会員].[会員ID]
    )
WHEN MATCHED THEN
    UPDATE SET
        [会員ID] = [会員].[会員ID],
        [優待区分] = [会員].[優待区分]
WHEN NOT MATCHED THEN
    INSERT
    (
        [会員ID],
        [優待区分]
    )
    VALUES
    (
        [会員].[会員ID],
        [会員].[優待区分]
    )
;

-- 優待会員テーブルに特定の会員テーブルのデータを更新

MERGE INTO

[優待会員]

USING

(

SELECT

[会員ID],

[氏名],

CASE

WHEN [在籍年数] >= 5 THEN 'P'

WHEN [在籍年数] >= 3 THEN 'G'

ELSE 'S'

END AS [優待区分]

FROM

[会員]

WHERE

-- 会員テーブルの在籍年数が1年以上の会員のみ対象にする

[会員].[在籍年数] >= 1

) AS [会員]

(

[優待会員].[会員ID] = [会員].[会員ID]

)

WHEN MATCHED THEN

UPDATE SET

[会員ID] = [会員].[会員ID],

[優待区分] = [会員].[優待区分]

WHEN NOT MATCHED THEN

INSERT

(

[会員ID],

[優待区分]

)

VALUES

(

[会員].[会員ID],

[会員].[優待区分]

)

;

このMEARGEステートメントでは会員テーブルの在籍年数が1年以上のレコードに対してのみ、優待会員テーブルのレコードと会員IDで照合を行い更新しています。

-- 優待会員テーブルをSELECT
SELECT * FROM [優待会員] ORDER BY [会員ID];

1 2	-- 優待会員テーブルをSELECT SELECT * FROM [優待会員] ORDER BY [会員ID];

実行結果

DELETEが実行される場合の例

最後に優待会員テーブルに存在するレコードが会員テーブルに存在しない場合に、優待会員テーブルのレコードが削除される例を記載します。
削除(DELETE)操作は、WHEN NOT MATCHED BY SOURCE THEN内に記述します。

会員テーブルの会員IDが「4」のレコードを削除します。

-- 会員IDが「4」のデータを削除
DELETE [会員] WHERE [会員ID] = 4;

1 2	-- 会員IDが「4」のデータを削除 DELETE [会員] WHERE [会員ID] = 4;

MERGEステートメントで会員テーブルと優待会員テーブルを会員IDをもとにマージします。

-- 会員テーブルのデータをもとに優待会員テーブルのデータを更新
MERGE INTO
    [優待会員]
USING
    (
    SELECT
        [会員ID],
        [氏名],
        CASE
            WHEN [在籍年数] >= 5 THEN 'P'
            WHEN [在籍年数] >= 3 THEN 'G'
            ELSE 'S'
            END AS [優待区分]
    FROM
        [会員]
    WHERE
        [会員].[在籍年数] >= 1 
    ) AS [会員]
ON
    (
    [優待会員].[会員ID] = [会員].[会員ID]
    )
WHEN MATCHED THEN
    -- 優待会員テーブル会員IDが会員テーブルに存在する場合は
    -- 優待会員テーブルのレコードを更新
    UPDATE SET
        [会員ID] = [会員].[会員ID],
        [優待区分] = [会員].[優待区分]
WHEN NOT MATCHED BY TARGET THEN
    -- 会員テーブルの会員IDが優待会員テーブルに存在しない場合は
    -- 優待会員テーブルにレコードを挿入
    INSERT
    (
        [会員ID],
        [優待区分]
    )
    VALUES
    (
        [会員].[会員ID],
        [会員].[優待区分]
    )
WHEN NOT MATCHED BY SOURCE THEN
    -- 優待会員テーブルの会員IDが会員テーブルに存在しない場合は
    -- 優待会員テーブルのレコードを削除
    DELETE
;

-- 会員テーブルのデータをもとに優待会員テーブルのデータを更新

MERGE INTO

[優待会員]

USING

(

SELECT

[会員ID],

[氏名],

CASE

WHEN [在籍年数] >= 5 THEN 'P'

WHEN [在籍年数] >= 3 THEN 'G'

ELSE 'S'

END AS [優待区分]

FROM

[会員]

WHERE

[会員].[在籍年数] >= 1

) AS [会員]

(

[優待会員].[会員ID] = [会員].[会員ID]

)

WHEN MATCHED THEN

-- 優待会員テーブル会員IDが会員テーブルに存在する場合は

-- 優待会員テーブルのレコードを更新

UPDATE SET

[会員ID] = [会員].[会員ID],

[優待区分] = [会員].[優待区分]

WHEN NOT MATCHED BY TARGET THEN

-- 会員テーブルの会員IDが優待会員テーブルに存在しない場合は

-- 優待会員テーブルにレコードを挿入

INSERT

(

[会員ID],

[優待区分]

)

VALUES

(

[会員].[会員ID],

[会員].[優待区分]

)

WHEN NOT MATCHED BY SOURCE THEN

-- 優待会員テーブルの会員IDが会員テーブルに存在しない場合は

-- 優待会員テーブルのレコードを削除

DELETE

;

-- 会員テーブルをSELECT
SELECT * FROM [会員] ORDER BY [会員ID];
-- 優待会員テーブルをSELECT
SELECT * FROM [優待会員] ORDER BY [会員ID];

-- 会員テーブルをSELECT

SELECT * FROM [会員] ORDER BY [会員ID];

-- 優待会員テーブルをSELECT

SELECT * FROM [優待会員] ORDER BY [会員ID];

WHEN NOT MATCHED BY SOURCE THEN内の操作が、優待会員テーブルに存在して会員テーブルに存在しないレコードの会員ID「4」に対して実行されます。
DELETEステートメントを記述することで優待会員テーブルの会員IDが「4」のレコードが削除されます。

MERGEステートメントを使用するとテーブル間のデータを簡単にマージできることがわかりました。
C#などのプログラムで更新対象のデータが存在するかどうかを確認して挿入(INSERT)、更新(UPDATE)、削除(DELETE)の処理を切り分けたり、SQLServerのストアドプロシージャでIF文を使用して処理を分岐するのもいいですが、MEARGEステートメントが使える環境であればスマート、且つシンプルに処理を行うことができるので、是非とも使ってみてください。

参考URL

MERGE (Transact-SQL)