SQLのマージ(MERGE)文を使ってみよう！INSERTまたはUPDATEを実行する

SQLのINSERT文とUPDATE文を既存テーブルのデータ有無により切り分けることが可能な、マージ(MERGE)文についてご紹介していきます。

便利なSQL文ではありますが、あまり知らない方も多いようですので、この機会にご確認ください。

SQLのマージ(MERGE)文の使い方

SQLのマージ文では、抽出元テーブルからSELECTした結果を、更新先テーブルに追加または更新します。

更新先テーブルに対象データが存在する場合には更新(UPDATE)、存在しない場合には追加(INSERT)が実行されます。

マージ文の書き方

まずは基本的な書式を確認しておきましょう。

MERGE INTO テーブル名1
USING (
  SELECT カラム名[, カラム名, ...]
  FROM テーブル名2
  [WHERE 条件式]
) 
ON (結合条件)
WHEN MATCHED THEN
  UPDATE SET
    カラム名 = 値
WHEN NOT MATCHED THEN
  INSERT (カラム名, ...)
  VALUES (値, ...)

テーブル名1に指定したテーブルを追加または更新する記述です。

テーブル名2に指定した条件のデータが存在するかを確認し、存在する場合には「WHEN MATCHED THEN」に指定したUPDATE文が実行されます。

存在しない場合には、「WHEN NOT MATCHED THEN」に指定したINSERT文が実行されます。

サンプル

サンプルとして次の2つのテーブルを用意しています。

user1

+------+--------------+------+
| id   | name         | age  |
+------+--------------+------+
|    1 | 山田太郎      |   30 |
|    2 | 山田花子      |   25 |
|    3 | 鈴木二郎      |   20 |
+------+--------------+------+

user2

+------+-----------------+------+
| id   | name            | age  |
+------+-----------------+------+
|    1 | 山田太郎         |   30 |
|    3 | 鈴木次郎         |   22 |
|    4 | 田中あきら       |   40 |
+------+-----------------+------+

では実際にサンプルのマージ文を作成してみましょう。

MERGE INTO user1 a
USING (
  SELECT id, name, age
  FROM user2
) b
ON (a.id = b.id)
WHEN MATCHED THEN
  UPDATE SET
    a.name = b.name,
    a.age  = b.age
WHEN NOT MATCHED THEN
  INSERT
  (id, name, age)
  VALUES
  (b.id, b.name, b.age)

実行した結果が下記の通りです。

user1

+------+--------------+------+
| id   | name         | age  |
+------+--------------+------+
|    1 | 山田太郎      |   30 |
|    2 | 山田花子      |   25 |
|    3 | 鈴木次郎      |   22 |
|    4 | 田中あきら    |   40 |
+------+--------------+------+

サンプルでは更新先テーブルである「user1」にid「1,2,3」のデータが格納されています。

id=1のデータに関しては、user1とuser2のデータが全く同じであるため、更新処理が実施されますが、表示される値自体は変わりません。

id=2のデータに関しては、user2のテーブルに存在しないため、追加も更新もされません。

id=3のデータに関しては、user2テーブル「name」「age」カラムの値がuser1テーブルに更新されていることをご確認頂けます。

id=4のデータに関しては、user1テーブルに存在しないため、追加(INSERT)処理が実施されています。

SQLマージ(MERGE)文の応用

SQLのマージ文では上述したように追加(INSERT)または更新(UPDATE)するのが一般的ですが、削除(DELETE)に関しても同様に実行させることが可能です。

応用編として追加・更新・削除をまとめて1つのSQL文で実行してみましょう。

ポテパンダの一言メモ

応用編でご紹介する「WHEN NOT MATCHED BY SOURCE THEN」を利用した削除処理は、SQL SERVERでの利用は確認出来ましたが、Oracleデータベースでは利用出来ないようでした。
ご利用の環境により利用可能なMerge文が若干異なるため、ご注意ください。

マージ文の書き方(応用編)

書き方は上述したマージ文とほとんど同じですが「WHEN NOT MATCHED」に少し追記が必要です。

MERGE INTO テーブル名1
USING (
  SELECT カラム名[, カラム名, ...]
  FROM テーブル名2
  [WHERE 条件式]
) 
ON (結合条件)
WHEN MATCHED THEN
  UPDATE SET
    カラム名 = 値
WHEN NOT MATCHED BY TARGET THEN
  INSERT (カラム名, ...)
  VALUES (値, ...)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

サンプル

では実際にサンプルで動作を確認してみましょう。

テーブルは上述したサンプルテーブルを利用します。

ポテパンダの一言メモ

1つ目のサンプルSQLを実行済みの方は、再度サンプルテーブルの状態にデータを戻しておいてください。

追加・更新・削除を1つのMERGE文にまとめると下記のようになります。

「WHEN NOT MATCHED」に「BY TARGET」オプションを付けると、更新先テーブル(user1)に条件と一致するデータが存在しない場合、追加(INSERT)処理が実施されます。

「BY SOURCE」オプションを付けると、利用元テーブル(user2)に条件と一致するデータが存在しない場合、更新先(user1)テーブルのデータが削除されます。

MERGE INTO user1 a
USING (
  SELECT id, name, age
  FROM user2
) b
ON (a.id = b.id)
WHEN MATCHED THEN
  UPDATE SET
    a.name = b.name,
    a.age  = b.age
WHEN NOT MATCHED BY TARGET THEN
  INSERT
  (id, name, age)
  VALUES
  (b.id, b.name, b.age)
WHEN NOT MATCHED BY SOURCE THEN
  DELETE;

実行した結果は下記の通りです。

+------+--------------+------+
| id   | name         | age  |
+------+--------------+------+
|    1 | 山田太郎      |   30 |
|    3 | 鈴木次郎      |   22 |
|    4 | 田中あきら    |   40 |
+------+--------------+------+

user1テーブルにのみ存在していた「id=2」のデータが削除されていますね。

さいごに：SQLのマージ(MERGE)文を利用して処理を1つにまとめよう

本記事では、SQLのマージ文の使い方についてご紹介してきました。

マージ文を利用することで、プログラム側で利用するSQLを切り替えていたような処理も、SQL側で追加・更新を切り替えることが可能となります。

一般的に利用されるのは、多くのデータベースで利用可能な追加・更新をマージ文で行う1つ目にご紹介した記述方法です。

2つ目にご紹介した削除を含めたマージ文は実行出来ないデータベースも多いようですので、あくまで知識の1つとして確認しておいてください。