同一條 SQL 中,先 SELECT 同表某些值,再 UPDATE/DELETE 該表。MySQL 禁止這種「自引用」以避免語義模糊。
DELETE FROM tbl
WHERE id IN (
SELECT MAX(id) FROM tbl a WHERE EXISTS (
SELECT 1 FROM tbl b WHERE a.tac = b.tac GROUP BY tac HAVING COUNT(1) > 1
)
GROUP BY tac
);
報錯:ERROR 1093 (HY000): You can't specify target table 'tbl' for update in FROM clause
將子查詢包裝一層,強制 MySQL 先物化結果集到臨時表,再作為外層 IN 列表來源,切斷「自引用」鏈路。
DELETE FROM tbl
WHERE id IN (
SELECT a.id FROM (
SELECT MAX(id) AS id FROM tbl a WHERE EXISTS (
SELECT 1 FROM tbl b WHERE a.tac = b.tac GROUP BY tac HAVING COUNT(1) > 1
)
GROUP BY tac
) a
);
SELECT MAX(id) ... 先執行,結果寫入內部臨時表(衍生表)。SELECT a.id FROM (...) a 讀取臨時表,不再直接讀取 tbl。DELETE 目標表與子查詢來源分離,滿足限制。DELETE t1 FROM tbl t1 JOIN (subquery) t2 ON t1.id=t2.id;SELECT MAX(id) INTO @ids FROM ...; DELETE FROM tbl WHERE id IN (@ids);(僅適用單值/逗號列表)。WITH cte AS (...) DELETE ...) 原生支援,更優雅。SQL Server、Oracle、PostgreSQL 均允許同表自引用更新/刪除,僅 MySQL 有此限制(為了簡化優化器邏輯)。
JOIN 或 CTE,語義清晰且效能穩定。IN (subquery),加一層衍生表 SELECT ... FROM (subquery) t 最省事。