MySQL DELETE 子查詢報錯與別名限制的解決方案

問題場景

在 MySQL 5.1.30 中執行帶子查詢的 DELETE 語句時遇到兩類常見問題：

問題一：同表子查詢限制

DELETE FROM table WHERE col NOT IN (SELECT col FROM table GROUP BY xx);

報錯：You can't specify target table 'table' for update in FROM clause

解決辦法：使用臨時表中轉

CREATE TABLE tmp SELECT col FROM table GROUP BY xx;
DELETE FROM table WHERE col NOT IN (SELECT col FROM tmp);
DROP TABLE tmp;

問題二：DELETE 不支援表別名

-- 失敗
DELETE FROM student a WHERE a.id IN (1,2);
-- 成功
DELETE FROM student WHERE id IN (1,2);
-- SELECT 則支援別名
SELECT a.* FROM student a WHERE a.id IN (1,2);

原因：MySQL 的 DELETE 語法在操作單表時不允許使用別名，這與 SELECT 不同。

最佳實踐總結

• 同表刪除時，優先用 JOIN 或臨時表繞過限制。
• 單表 DELETE 直接寫表名，不要加別名。
• 複雜刪除邏輯建議先用 SELECT 驗證資料範圍，再轉為 DELETE。
• 考慮升級至 MySQL 8.0+，支援 CTE (WITH 子句) 寫法更優雅。