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ステートメントについてご紹介します。

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

本記事では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

データのINSERT

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ステートメントが実行される場合の例を以下に示します。

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

実行結果

MERGEステートメントでINSERTされた結果

UPDATEが実行される場合の例

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

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

実行結果

MERGEステートメントでUPDATEされた結果

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

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

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

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

実行結果

MERGEステートメントで会員テーブルのデータを優待会員テーブルに更新した結果

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

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

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

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

実行結果

MERGEステートメントで会員テーブルの特定のデータを優待会員テーブルに更新した結果

DELETEが実行される場合の例

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

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

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

MERGEステートメントで会員テーブルデータと優待会員テーブルのデータをマージした結果

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

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

参考URL

MERGE (Transact-SQL)