UPSERT（ON CONFLICT）と一括 INSERT 応用

本記事で使うデータ — stock と stock_in

本記事のUPSERT（UPDATE と INSERT を合わせた造語です。

「あれば更新・なければ挿入」を 1 文で行う操作）は、INSERT … ON CONFLICT(キー) DO UPDATE構文で実現します。

主キーや UNIQUE 制約と衝突したら更新、衝突しなければそのまま挿入されます。

演習に入る前に、本記事で使う 2 つのテーブル — stockとstock_in — の列定義とデータのサンプルを確認しておきます。

① PRAGMA table_info(stock);でstockの列名・型・主キーを確認してください（skuが主キーであることが UPSERT の前提です）。

② SELECT * FROM stock ORDER BY sku;とSELECT * FROM stock_in;で両テーブルのデータをプレビューしてください。

ON CONFLICT DO UPDATE — あれば更新・なければ挿入

INSERT INTO 表(...) VALUES (...) ON CONFLICT(キー列) DO UPDATE SET 列 = ...と書くと、INSERTしようとした行がON CONFLICTで指定したキー（主キーや UNIQUE 列）と衝突したらDO UPDATEの更新が実行され、衝突しなければそのまま挿入されます。

DO UPDATEの中では特別なテーブル名excludedで「挿入しようとした行の値」を参照できます。

qty = qty + excluded.qtyと書くと、衝突した場合に「既存のqty」へ「挿入しようとしたqty」を足し込めます。

qty = excluded.qtyなら上書き、qty = qty + excluded.qtyなら加算と、用途に応じて使い分けます。

-- 既存 sku に衝突 → DO UPDATE で qty を加算
INSERT INTO stock(sku, name, qty, price)
VALUES ('A001', 'Pen', 5, 80)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

-- 新規 sku → 衝突しないのでそのまま挿入
INSERT INTO stock(sku, name, qty, price)
VALUES ('A006', 'Marker', 3, 120)
ON CONFLICT(sku) DO UPDATE SET qty = qty + excluded.qty;

SELECT sku, name, qty FROM stock WHERE sku IN ('A001', 'A006');

「入庫処理で、既存 sku は在庫を加算し、未登録 sku は新規登録したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① UPSERT 前にSELECT sku, name, qty FROM stock WHERE sku IN ('A001','A006') ORDER BY sku;で、A001 が既存で A006 が未登録であることを確認してください。

② 既存の A001 に対し、nameを Pen、qtyを 15、priceを 80 でUPSERTしてください。衝突時はqtyを既存値に挿入値を加算する（上書きではない）形にしてください。

③ 未登録の A006 に対し、nameを Stapler、qtyを 3、priceを 200 で同じUPSERTをしてください（衝突しないので新規挿入されます）。

④ 最後にもう一度同じSELECTを流し、A001 が加算され A006 が追加されたことを確認してください。

DO NOTHING — 衝突したら何もしない

更新も挿入もしたくない、つまり「衝突したら静かにスキップしたい」場合はON CONFLICT(キー) DO NOTHINGを使います。

衝突しなければ挿入され、衝突すればその行はエラーにならず無視されます。

下の例では、既存の A002 をDO NOTHINGで挿入しようとすると衝突して無視され、未登録の A007 は新規挿入されます。

-- 衝突したら無視（DO NOTHING）
INSERT INTO stock(sku, name, qty, price)
VALUES ('A002', 'Note', 999, 999)
ON CONFLICT(sku) DO NOTHING;

-- A002 は元の値（qty 60 / price 250）のまま変わらない
SELECT sku, name, qty, price FROM stock WHERE sku = 'A002';

「在庫マスタへ複数行を取り込む際、既存 sku は絶対に書き換えず、未登録のものだけ追加したい」という要件を想定します。

① UPSERT 前にSELECT sku, name, qty, price FROM stock WHERE sku IN ('A003','A007') ORDER BY sku;で、A003 が既存で A007 が未登録であることを確認してください。

② 既存の A003 をname Clip、qty 0、price 0 で挿入しようとし、衝突したら何もしない（DO NOTHING） UPSERT を書いてください。

③ 未登録の A007 をname Eraser、qty 50、price 90 で同じDO NOTHINGの UPSERT で追加してください。

④ 最後にもう一度同じSELECTを流し、A003 が変わっていないこと・A007 が追加されたことを確認してください。

複数行 UPSERT — 一括 INSERT と ON CONFLICT を組み合わせる

VALUESを(...),(...),(...)とカンマで並べる複数行 INSERTにON CONFLICTを付けると、各行が独立に「衝突なら更新・なければ挿入」されます。

入庫データをまとめて 1 文で反映でき、行ごとにUPDATEとINSERTを呼び分ける手続き的な処理を 1 つの SQL に置き換えられます。

excludedは複数行 UPSERT でも「その行で挿入しようとした値」を指すため、qty = qty + excluded.qtyと書けば、既存行は加算、新規行はそのまま挿入される、を行単位で適用できます。

本記事の最後の演習として、stock_inの 3 行（A001 / A004 / A006）を一括 UPSERT します。

-- 複数行をまとめて UPSERT: 既存は qty を加算しつつ price も最新化、新規は挿入
INSERT INTO stock(sku, name, qty, price)
VALUES
  ('A002', 'Note', 10, 260),
  ('A005', 'Glue', 20, 190),
  ('A007', 'Ruler', 15, 90)
ON CONFLICT(sku) DO UPDATE
  SET qty = qty + excluded.qty,
      price = excluded.price;

SELECT sku, name, qty, price FROM stock ORDER BY sku;

「入庫テーブルstock_inの 3 行を 1 文でまとめて在庫に反映したい」という要件を想定します。本記事の最後の演習です。

① UPSERT 前にSELECT sku, name, qty FROM stock WHERE sku IN ('A001','A004','A006') ORDER BY sku;で、A001 / A004 が既存・A006 が未登録であることを確認してください。

② stockに対し、A001（name Pen / qty 50 / price 80）、A004（name Tape / qty 100 / price 150）、A006（name Marker / qty 30 / price 120）の3 行を 1 つの INSERTで投入し、ON CONFLICT(sku) DO UPDATEで衝突時はqtyを加算する一括 UPSERT を書いてください。

③ 最後にSELECT sku, name, qty FROM stock ORDER BY sku;を置き、A001 / A004 は加算され、A006 が新規追加されていることを確認してください。