SQL練習室
← SQLガイド

SQLのMERGE文とは?あれば更新・なければ追加を1文で書く使い方とON CONFLICTとの違い

MERGEは、更新元の表と突き合わせて、一致した行は更新・削除し、一致しない行は追加する文です。「あれば更新、なければ追加」を1つのSQLで書けます。商品の在庫表に入荷データを反映する例で、WHENごとの動きと、INSERT ... ON CONFLICTとの違いを確かめましょう。

例で使う表

productsは1行が商品1件で、stockは在庫数です。arrivalsは入荷データで、qtyが入荷数です。入荷のid 2・3・4はproductsに既にあり、id 5(付箋)はproductsにまだありません。消しゴムの在庫は0、定規の入荷数も0です。以降の各例は、この更新前の状態から始め、実行後のproductsをid順に表示した結果を示します。

products:商品4件(更新前)
idnamestock
1ノート50
2ペン100
3消しゴム0
4定規30
arrivals:入荷4件
idnameqty
2ペン20
3消しゴム40
4定規0
5付箋60

MERGEの基本構文

sql
MERGE INTO products
USING arrivals ON products.id = arrivals.id
WHEN MATCHED THEN
  UPDATE SET stock = products.stock + arrivals.qty
WHEN NOT MATCHED THEN
  INSERT (id, name, stock) VALUES (arrivals.id, arrivals.name, arrivals.qty);

MERGEは一致すれば更新、しなければ追加する

MERGE INTO 更新する表 USING 更新元 ON 一致の条件 の後に、WHEN句を並べます。ON products.id = arrivals.id で、入荷の行と同じidの商品を探します。見つかった行(MATCHED)は在庫に入荷数を足し、見つからない行(NOT MATCHED)はproductsへ追加します。ペンは100に20を足して120、消しゴムは40、定規は0を足して30のまま、付箋は新しく60で追加されます。

sql
MERGE INTO products
USING arrivals ON products.id = arrivals.id
WHEN MATCHED THEN
  UPDATE SET stock = products.stock + arrivals.qty
WHEN NOT MATCHED THEN
  INSERT (id, name, stock) VALUES (arrivals.id, arrivals.name, arrivals.qty);

SELECT * FROM products ORDER BY id;
実行結果
idnamestock
1ノート50
2ペン120
3消しゴム40
4定規30
5付箋60

WHEN MATCHED THEN UPDATEだけなら既存の行だけ更新する

WHEN NOT MATCHED を書かなければ、一致しない入荷行は無視されます。付箋はproductsにないため追加されず、ペンと消しゴムの在庫だけが増えます。商品表に載っていないものを勝手に増やしたくないときの書き方です。更新側のSETでは、更新前の値を products.stock、更新元の値を arrivals.qty のように表名つきで区別します。

sql
MERGE INTO products
USING arrivals ON products.id = arrivals.id
WHEN MATCHED THEN
  UPDATE SET stock = products.stock + arrivals.qty;

SELECT * FROM products ORDER BY id;
実行結果
idnamestock
1ノート50
2ペン120
3消しゴム40
4定規30

WHEN NOT MATCHED THEN INSERTだけなら新しい行だけ追加する

WHEN MATCHED を書かなければ、既にある商品は変更されません。付箋だけが追加され、ペンの在庫は100のままです。INSERTは列名を並べ、VALUESには更新元(arrivals)の列を指定します。追加する行にはまだ対応するproductsの行がないため、VALUESでは更新する表の列は使えません。

sql
MERGE INTO products
USING arrivals ON products.id = arrivals.id
WHEN NOT MATCHED THEN
  INSERT (id, name, stock) VALUES (arrivals.id, arrivals.name, arrivals.qty);

SELECT * FROM products ORDER BY id;
実行結果
idnamestock
1ノート50
2ペン100
3消しゴム0
4定規30
5付箋60

WHEN MATCHED AND 条件で更新する行を絞る

WHEN句にはANDで条件を付けられ、更新する表と更新元のどちらの列も使えます。在庫が0の商品だけを補充する条件 products.stock = 0 を付けると、消しゴムだけが0から40になります。ペンは一致していても条件に合わないので100のままで、定規も30のままです。一致して条件に合わない行は何もされず、そのまま残ります。

sql
MERGE INTO products
USING arrivals ON products.id = arrivals.id
WHEN MATCHED AND products.stock = 0 THEN
  UPDATE SET stock = arrivals.qty
WHEN NOT MATCHED THEN
  INSERT (id, name, stock) VALUES (arrivals.id, arrivals.name, arrivals.qty);

SELECT * FROM products ORDER BY id;
実行結果
idnamestock
1ノート50
2ペン100
3消しゴム40
4定規30
5付箋60

WHEN MATCHED THEN DELETEで一致した行を削除する

MERGEのアクションにはUPDATEとINSERTのほかにDELETEもあります。ここでは「入荷数0は取扱終了の連絡」として、qtyが0の定規を削除します。WHENは書いた順に調べられ、最初に条件が成り立ったものだけが実行されます。DELETEの条件を先に、条件のない更新を後に書くことで、定規は削除、ペンと消しゴムは更新になります。条件のない更新を先に書くと、後ろのWHENに届かないため、PostgreSQLはエラーにします。

sql
MERGE INTO products
USING arrivals ON products.id = arrivals.id
WHEN MATCHED AND arrivals.qty = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET stock = products.stock + arrivals.qty
WHEN NOT MATCHED THEN
  INSERT (id, name, stock) VALUES (arrivals.id, arrivals.name, arrivals.qty);

SELECT * FROM products ORDER BY id;
実行結果
idnamestock
1ノート50
2ペン120
3消しゴム40
5付箋60

INSERT ... ON CONFLICTとの使い分け

追加と更新だけで済み、一意な列(主キーなど)の重複で判定できるなら、INSERT ... ON CONFLICT DO UPDATE でも同じ結果になります。この例では1つ目の節と同じ表になります。MERGEはON条件を自由に書け、DELETEや条件つきのWHENも使えますが、ON CONFLICTは一意制約・一意インデックスが前提です。公式ドキュメントでは、ON CONFLICT DO UPDATEは同時実行でも「追加か更新のどちらか」を保証すると説明されています。MERGEはこの保証を持たないため、同時に実行される追加が多い処理ではON CONFLICTを検討します。

sql
INSERT INTO products (id, name, stock)
SELECT id, name, qty FROM arrivals
ON CONFLICT (id) DO UPDATE
  SET stock = products.stock + EXCLUDED.stock;

SELECT * FROM products ORDER BY id;
実行結果
idnamestock
1ノート50
2ペン120
3消しゴム40
4定規30
5付箋60

MERGEでよくある間違い

条件のないWHENを先に書くとエラーになる

WHENは書いた順に調べられ、最初に条件が成り立ったものだけが実行されます。条件のないWHEN MATCHEDを先に書くと、同じ種類の後ろのWHENには届かないため、PostgreSQLは「unreachable WHEN clause」のエラーにします。DELETEや条件つきの更新のように条件の厳しいWHENを先に、条件のないものを最後に書きます。

更新する表の1行が更新元の複数行と一致するとエラーになる

ONの条件で、更新する表の1行が更新元の複数行と一致すると、MERGEはエラーになります。公式ドキュメントも、更新する表の各行が更新元の1行だけと結合するようにと説明しています。入荷が同じ商品で複数行ある場合は、USINGでGROUP BYして商品ごとに1行へまとめてから突き合わせます。

実際に1問解いてみる

MERGE

arrivals(入荷データ)をproductsに反映してください。idが一致する商品は在庫に入荷数(qty)を足し、一致しない商品はproductsに追加します(stockは入荷数)。1つのMERGE文で書きます。

テーブル

読み込み中…

実行結果

関連構文

MERGEについてよくある質問

MERGEとINSERT ... ON CONFLICTはどちらを使えばよいですか?
一意制約の重複で「あれば更新、なければ追加」を判定でき、同時実行が多いならON CONFLICTが向きます。一致しない行だけ追加する、一致した行を条件で更新・削除するなど、1つの文で複数の処理を分けたいときはMERGEが向きます。
WHENに合わない行はどうなりますか?
一致する行がなく、WHENの条件にも合わない行は、何もされず残ります。この例では、WHEN MATCHEDだけを書くと付箋は追加されません。エラーにはなりません。
MERGEで何行変更されたか分かりますか?
実行結果のコマンドタグに、追加・更新・削除した行の合計が「MERGE 件数」で返ります。PostgreSQL 17以降はRETURNING句で、変更された行の内容も取得できます。
UPDATEの条件でWHEREは使えますか?
MERGEのUPDATE・DELETEの中にはWHEREを書けません。行を絞る条件は、WHEN MATCHED AND 条件 の形で書きます。

参考文献

内容はPostgreSQL公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。