MySQL 子查詢更新同表錯誤完整解決方案

錯誤訊息：You can't specify target table 'tbl' for update in FROM clause

核心原因： MySQL 禁止在同一條 SQL 語句中，先 SELECT 查詢目標表，再對同一表進行 UPDATE/DELETE 操作。這是為了防止資料不一致與死鎖風險的保護機制。僅 MySQL 有此限制，SQL Server、Oracle、PostgreSQL 均支援此操作。

經典觸發場景

刪除重複資料，保留每組最大 ID：

-- ❌ 錯誤寫法：直接引用同表子查詢
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
);

三種標準解決方案

方案一：衍生表/中間表包裝 (推薦，相容性最好)

將子查詢結果包裝為衍生表，打破「直接引用」限制：

-- ✅ 正確寫法：增加一層 SELECT * FROM (...) 包裝
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
);

原理： MySQL 會先執行最內層子查詢建立臨時結果集，外層引用的是臨時表而非原表，繞過限制。

方案二：多表 DELETE 語法 (MySQL 專有，效能較好)

DELETE t1 FROM tbl t1
INNER JOIN (
    SELECT MAX(id) AS max_id FROM tbl 
    WHERE tac IN (
        SELECT tac FROM tbl GROUP BY tac HAVING COUNT(*) > 1
    )
    GROUP BY tac
) t2 ON t1.id = t2.max_id;

優勢： 避免 IN 子查詢效能問題，利用 JOIN 優化器。

方案三：使用變數/暫存表 (大數據量首選)

-- 步驟 1：建立暫存表存放要刪除的 ID
CREATE TEMPORARY TABLE tmp_ids AS
SELECT MAX(id) AS id FROM tbl 
WHERE tac IN (SELECT tac FROM tbl GROUP BY tac HAVING COUNT(*) > 1)
GROUP BY tac;

-- 步驟 2：多表刪除
DELETE t FROM tbl t INNER JOIN tmp_ids tmp ON t.id = tmp.id;

-- 步驟 3：清理
DROP TEMPORARY TABLE tmp_ids;

適用： 百萬級以上資料，避免長事務鎖表。

效能對比與選擇指南

根本規避策略：應用層分離

最佳實踐：應用程式層面分兩步執行

# 步驟 1：查詢要刪除的 ID (應用層執行)
ids = SELECT MAX(id) FROM tbl ... GROUP BY tac;

# 步驟 2：應用層組裝 IN 列表執行刪除
DELETE FROM tbl WHERE id IN (id1, id2, id3...);

優勢： 完全繞過資料庫限制、可加入業務邏輯驗證、支援分頁批次刪除避免長鎖。

版本演進提示

MySQL 8.0+ 引入 CTE (Common Table Expressions)，可寫更優雅的：

WITH to_delete AS (
    SELECT MAX(id) AS id FROM tbl 
    WHERE tac IN (SELECT tac FROM tbl GROUP BY tac HAVING COUNT(*) > 1)
    GROUP BY tac
)
DELETE t FROM tbl t JOIN to_delete d ON t.id = d.id;

但本質仍是衍生表機制，核心限制未改變。