サブクエリ・JOIN を使う UPDATE / DELETE

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

本記事では、別のテーブルや集計結果を使って更新・削除する一段高度な書き方を扱います。

具体的には、相関サブクエリ（外側の行ごとに評価される、内側の SELECT が外側の列を参照するサブクエリ）で値を計算する更新、UPDATE … FROMによる別テーブルとの結合更新、サブクエリをWHEREに置くDELETEの 3 つです。

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

① PRAGMA table_info(stock);でstockの列名・型・主キーを確認してください。

② SELECT * FROM stock LIMIT 5;とSELECT * FROM stock_in;で両テーブルのデータをプレビューしてください。stock_inにstock側へ存在しない sku が含まれている点も観察してください。

相関サブクエリで別テーブルの値を使って更新する

UPDATE 表 SET 列 = (SELECT ... WHERE サブクエリ側.キー = 表.キー)と書くと、更新対象の行ごとに内側の SELECT が評価され、その結果で列を書き換えられます。

内側の SELECT が外側のUPDATE対象の列（stock.sku）を参照するため、これを相関サブクエリと呼びます。

注意点は、stock_inに対応行が無いstockの行では内側の SELECT が NULL を返すことです。

-- COALESCE を使わない例: 対応行が無い sku の qty が NULL で上書きされる
UPDATE stock
SET qty = qty + (SELECT SUM(add_qty) FROM stock_in WHERE stock_in.sku = stock.sku);

qty = qty + (SELECT ...)のまま実行すると、入庫が無い行のqtyが NULL で上書きされてしまいます。

これを防ぐため、COALESCE(サブクエリ, 0)で NULL を 0 に変換するか、WHERE EXISTS (...)で「入庫がある行だけ」に対象を絞ります。

-- まず SELECT で更新後の値を確認（実行しても stock は変わらない）
SELECT sku, qty,
  qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku) AS new_qty
FROM stock;

-- 想定どおりなら UPDATE に書き換え（COALESCE で NULL を 0 に変換）
UPDATE stock
SET qty = qty + (SELECT COALESCE(SUM(add_qty), 0) FROM stock_in WHERE stock_in.sku = stock.sku);

「入庫テーブルstock_inの数量を、対応するstockの在庫qtyに反映したい」という要件を想定します。（正しく実行できれば解説が表示されます）

① まずSELECT sku, qty FROM stock ORDER BY sku;で実行前の状態を確認してください。

② COALESCE版として、stockのqtyにstock_in.add_qtyの合計を足し込む相関サブクエリ更新を書いてください。stock_inに対応行が無い sku は NULL を 0 として扱い、元の値のままになるようにしてください。実行後にもう一度SELECTで値を確認してください。

③ 続けてWHERE EXISTS (...)で「入庫のある行だけ」に絞ったEXISTS 版の更新も書いてください。実行後にもう一度SELECTで値を確認し、A001 / A004 にさらに同じ入庫数が加算されている（二重に足し込まれている）ことを観察してください。

UPDATE … FROM で別テーブルと結合して更新する

UPDATE 表 SET 列 = 値 FROM 別テーブル WHERE 結合条件と書くと、UPDATEの対象表とFROMで指定した別テーブルを結合してから更新でき、結合先の列をそのままSETに書けます。

-- UPDATE … FROM の例: 入庫予定のある商品だけ価格を 10 上げる
UPDATE stock
SET price = price + 10
FROM stock_in
WHERE stock.sku = stock_in.sku;

-- 結合できた sku（A001 / A004）だけ price が上がる
SELECT sku, price FROM stock ORDER BY sku;

FROM側のstock_inに対応行が無いstockの行は、WHEREの結合条件が成立せず更新対象から自動的に外れます。

相関サブクエリのように NULL 化を別途防ぐ必要がない点が利点です。

「相関サブクエリと同じ在庫反映を、UPDATE … FROMでより短く書きたい」という要件を想定します。

① stockを更新対象、stock_inをFROM側に置き、skuで結合してqtyにadd_qtyを足し込むUPDATE … FROMを書いてください。

② 同じスクリプトの最後にSELECT sku, qty FROM stock ORDER BY sku;を置き、結合できた A001 / A004 だけ在庫が増えていることを確認してください。

サブクエリ条件の DELETE と影響行数の確認

DELETE FROM 表 WHERE 列 IN (SELECT ...)やWHERE EXISTS (SELECT ...)のように、DELETEのWHEREにもサブクエリを置けます。

これで「別テーブルに存在する／しない行だけを削除する」「集計結果が条件を満たす行を削除する」といった、固定値では表せない削除ができます。

削除は元に戻せない破壊的な操作なので、本番運用では先に同じWHEREでSELECT COUNT(*)を実行して影響行数を確認してからDELETEに書き換えるのが基本動作です。

実行後はSELECT COUNT(*) FROM 表;で残り行数を確かめ、想定どおり削除されたかを検証します。

-- まず削除対象を確認: 平均価格を下回る商品（実行しても stock は変わらない）
SELECT sku, name, price FROM stock
WHERE price < (SELECT AVG(price) FROM stock);

-- 想定どおりなら DELETE に書き換え、残り行数を確認
DELETE FROM stock
WHERE price < (SELECT AVG(price) FROM stock);
SELECT COUNT(*) AS remaining FROM stock;

「在庫が 0 で、かつ入庫予定（stock_in）も無い廃番候補の sku をstockから削除したい」という要件を想定します。本記事の最後の演習なので、テーブル本体から行を削除する破壊的な操作を行います。

① stockから「qtyが 0」かつ「skuがstock_inに存在しない」行を削除するサブクエリ条件付き DELETEを書いてください。

② 削除のあとにSELECT COUNT(*) AS remaining FROM stock;を置き、削除後に残った行数を確認してください。A005（Glue / qty 0 / 入庫なし）が消えていることをSELECT * FROM stock ORDER BY sku;で確かめても構いません。