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順に表示した結果を示します。
| id | name | stock |
|---|---|---|
| 1 | ノート | 50 |
| 2 | ペン | 100 |
| 3 | 消しゴム | 0 |
| 4 | 定規 | 30 |
| id | name | qty |
|---|---|---|
| 2 | ペン | 20 |
| 3 | 消しゴム | 40 |
| 4 | 定規 | 0 |
| 5 | 付箋 | 60 |
MERGEの基本構文
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で追加されます。
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;| id | name | stock |
|---|---|---|
| 1 | ノート | 50 |
| 2 | ペン | 120 |
| 3 | 消しゴム | 40 |
| 4 | 定規 | 30 |
| 5 | 付箋 | 60 |
WHEN MATCHED THEN UPDATEだけなら既存の行だけ更新する
WHEN NOT MATCHED を書かなければ、一致しない入荷行は無視されます。付箋はproductsにないため追加されず、ペンと消しゴムの在庫だけが増えます。商品表に載っていないものを勝手に増やしたくないときの書き方です。更新側のSETでは、更新前の値を products.stock、更新元の値を arrivals.qty のように表名つきで区別します。
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;| id | name | stock |
|---|---|---|
| 1 | ノート | 50 |
| 2 | ペン | 120 |
| 3 | 消しゴム | 40 |
| 4 | 定規 | 30 |
WHEN NOT MATCHED THEN INSERTだけなら新しい行だけ追加する
WHEN MATCHED を書かなければ、既にある商品は変更されません。付箋だけが追加され、ペンの在庫は100のままです。INSERTは列名を並べ、VALUESには更新元(arrivals)の列を指定します。追加する行にはまだ対応するproductsの行がないため、VALUESでは更新する表の列は使えません。
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;| id | name | stock |
|---|---|---|
| 1 | ノート | 50 |
| 2 | ペン | 100 |
| 3 | 消しゴム | 0 |
| 4 | 定規 | 30 |
| 5 | 付箋 | 60 |
WHEN MATCHED AND 条件で更新する行を絞る
WHEN句にはANDで条件を付けられ、更新する表と更新元のどちらの列も使えます。在庫が0の商品だけを補充する条件 products.stock = 0 を付けると、消しゴムだけが0から40になります。ペンは一致していても条件に合わないので100のままで、定規も30のままです。一致して条件に合わない行は何もされず、そのまま残ります。
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;| id | name | stock |
|---|---|---|
| 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はエラーにします。
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;| id | name | stock |
|---|---|---|
| 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を検討します。
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;| id | name | stock |
|---|---|---|
| 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公式ドキュメントを参照し、初めて学ぶ人向けに日本語で整理しています。
- PostgreSQL公式:MERGE — WHEN句、条件、アクション、1行と1行の対応、INSERT ... ON CONFLICTとの関係
- PostgreSQL公式:INSERT(ON CONFLICT句) — ON CONFLICT DO UPDATEの仕様と同時実行時の保証