状況
同じテーブルを参照するサブクエリを使って行を更新しようとしています — 重複レコードの削除、集計に基づく行のフラグ付け、孤立エントリのクリーンアップなど。クエリは一見まったく問題なく見えます。しかし、MySQLは次のエラーをスローします:
ERROR 1093 (HY000): You can't specify target table 'orders' for update in FROM clause
典型的な例を示します — 同じ customer_id が複数回出現する重複注文にマークを付ける処理です:
-- 同じcustomer_idを持つ別の注文が存在する場合、その注文を'duplicate'としてマークする
UPDATE orders
SET status = 'duplicate'
WHERE id NOT IN (
SELECT MIN(id)
FROM orders
GROUP BY customer_id
);
MySQLは、WHERE句のサブクエリが orders を読み取りながら、同時に orders をUPDATEの対象にしているため、このクエリを拒否します。単一のステートメントで同じテーブルへの読み取りと書き込みを行うことは — 少なくとも直接的には — 許可されていません。
MySQLがこの制限を設ける理由
問題は曖昧性にあります。サブクエリは元の行を読み取るべきか、それとも更新の途中で変更された行を読み取るべきか？MySQLはこの問いに明確に答えられないため、ステートメント全体をブロックします。
PostgreSQLとSQL Serverは、サブクエリを先にマテリアライズすることでこの問題を回避します — つまり、書き込みが発生する前にデータのスナップショットを取ります。MySQLのオプティマイザはより厳格な立場を取り、処理全体を拒否します。解決策は、サブクエリを派生テーブルでラップすることで、そのマテリアライズを自分で強制することです。
簡単な修正: サブクエリをさらにSELECTでラップする
内部クエリの周りにSELECTのレイヤーをもう一つ追加します。MySQLは外側のラッパーを派生テーブル — 匿名の一時的な結果セット — として扱うため、直接的な参照の循環が解消されます:
-- サブクエリをさらにSELECTでラップして派生テーブルを作成する
UPDATE orders
SET status = 'duplicate'
WHERE id NOT IN (
SELECT id FROM (
SELECT MIN(id) AS id
FROM orders
GROUP BY customer_id
) AS tmp
);
注意点が2つあります。まず、AS tmp エイリアスは必須です — MySQLは名前のない派生テーブルを拒否します。次に、内部クエリが実行されて tmp にマテリアライズされてからUPDATEが実行されるため、競合は発生しません。
より良い修正: JOINを使って書き直す
JOINを使った更新は、特に10,000行以上のテーブルでは、ネストされたサブクエリよりもクリーンで高速なことが多いです。MySQLはJOINを通じて直接行を変更することをサポートしています:
-- JOIN UPDATEとして書き直す
UPDATE orders o
JOIN (
SELECT MIN(id) AS min_id, customer_id
FROM orders
GROUP BY customer_id
) AS keep_ids ON o.customer_id = keep_ids.customer_id
SET o.status = 'duplicate'
WHERE o.id != keep_ids.min_id;
ロジックは同じで、構造が異なります。JOIN内の派生テーブルは、どの行にも触れる前に独立して計算されます。曖昧性はありません。また、クエリプランナーは相関サブクエリよりもJOINの方がインデックスを効果的に最適化できます。
同じエラーが発生するもう一つのパターン: DELETE
ERROR 1093はDELETEでも同様に発生します:
-- 同じエラーで失敗する
DELETE FROM orders
WHERE id NOT IN (
SELECT MIN(id) FROM orders GROUP BY customer_id
);
修正方法も同じです — ラップするSELECTを追加します:
DELETE FROM orders
WHERE id NOT IN (
SELECT id FROM (
SELECT MIN(id) AS id
FROM orders
GROUP BY customer_id
) AS tmp
);
MySQL 8.x: CTEを使う
MySQL 8.0以降では、CTEが最もクリーンな選択肢です。MySQL 8はメインステートメントを実行する前に、CTEを独立したステップとしてマテリアライズします — まさに必要な動作です:
-- MySQL 8.0以降のみ
WITH keepers AS (
SELECT MIN(id) AS id
FROM orders
GROUP BY customer_id
)
UPDATE orders
SET status = 'duplicate'
WHERE id NOT IN (SELECT id FROM keepers);
ネストされたラッピングよりもはるかに読みやすいです。CTEは一度実行されてクリーンな結果セットを生成し、UPDATEはそこから読み取ります。競合なし、回避策も不要です。
コミット前に修正を確認する
UPDATEを実行する前に、SELECTに置き換えて影響を受ける行をプレビューします:
-- 影響を受ける行をプレビューする
SELECT id, customer_id, status
FROM orders
WHERE id NOT IN (
SELECT id FROM (
SELECT MIN(id) AS id
FROM orders
GROUP BY customer_id
) AS tmp
);
結果セットが期待通りであれば、サブクエリのロジックは正しいです。UPDATEを実行し、影響を受けた行数が正しいことを確認します:
-- UPDATE後
SELECT status, COUNT(*) FROM orders GROUP BY status;
クイックリファレンス
- 根本原因: MySQLは単一のステートメントで同じテーブルへの読み取りと書き込みをブロックする
- 修正1: サブクエリをラップ —
SELECT id FROM (...) AS tmp - 修正2:
UPDATE ... JOIN (subquery) AS tmpとして書き直す — 大きなテーブルに適している - 修正3: MySQL 8.0以降 — 最もクリーンな構文のためにCTEを使用する
- 常にエイリアスを付ける 派生テーブルには必ずエイリアスを付けること。そうしないと「every derived table must have its own alias」という別のエラーが発生する
- まずテストする 実際のUPDATEやDELETEを実行する前にSELECTで確認する