MySQL 進階技巧：JOIN 更新與資料去重實戰指南

前言

JOIN 不僅用於查詢，還能支援跨表更新與高效去重。本文系統整理 JOIN 類型視覺化理解、避開 ERROR 1093 的更新寫法、以及 GROUP BY ... HAVING 去重最佳實踐。

一、JOIN 類型一覽（建議搭配 Venn 圖記憶）

• INNER JOIN：雙方均有匹配行才保留。
• LEFT JOIN：左表全保留，右表無匹配則 NULL。
• RIGHT JOIN：右表全保留，左表無匹配則 NULL。
• FULL JOIN：MySQL 不直接支援，可用 UNION 模擬。
• CROSS JOIN：笛卡兒積，慎用於大表。

參考經典圖解：A Visual Explanation of SQL Joins

二、跨表更新避坑：ERROR 1093 解決方案

需求：將 t1 與 t2 中 col_a 重複的行在 t1 更新為 'hi'。

❌ 直接子查詢報錯

UPDATE t1 SET col_a = 'hi'
WHERE t1.col_a IN (
  SELECT b.col_a FROM t1 a INNER JOIN t2 b ON a.col_a = b.col_a
); -- ERROR 1093: You can't specify target table 't1' for update in FROM clause

✅ 改寫為 JOIN 衍生表

UPDATE t1 aa
JOIN (
  SELECT b.col_a FROM t1 a INNER JOIN t2 b ON a.col_a = b.col_a
) bb ON aa.col_a = bb.col_a
SET aa.col_a = 'hi';

原理：衍生表 bb 物質化後與 t1 連接，規避了「同表更新同時讀取」的限制。

三、資料去重：查重與刪重標準姿勢

1. 查詢重複資料

SELECT col_a, COUNT(*) AS cnt
FROM t1
GROUP BY col_a
HAVING cnt > 1;

2. 刪除重複，保留 ID 最大的一行

DELETE a
FROM t1 a
JOIN (
  SELECT col_a, MAX(id) AS keep_id
  FROM t1
  GROUP BY col_a
  HAVING COUNT(*) > 1
) b ON a.col_a = b.col_a
WHERE a.id < b.keep_id;

關鍵點：

• 子查詢先算出每組保留的 MAX(id)。
• 外層 JOIN 定位需刪除的行（id < keep_id）。
• 單條 SQL 完成，避免游標/迴圈效能差。

四、進階去重場景擴展

• 多欄位組合去重：GROUP BY col_a, col_b, col_c。
• 保留最早/最新時間：MAX(create_time) 或 MIN(create_time)。
• 大表分批刪除：配合 LIMIT 迴圈，避免長事務鎖表。

五、總結

掌握 JOIN 更新轉衍生表 與 GROUP BY HAVING + JOIN 刪重 兩大模式，能解決 90% 的跨表寫入與去重需求。實際上線前務必在備庫驗證影響行數，並備份關鍵資料。