MySQL 多表關聯 UPDATE 實戰：JOIN 語法替代子查詢

問題場景

物料表 materials(y_name, y_quantity)，產品表 pro_a(p_name, p_quantity)。需將產品用量彙總累加到物料表。

❌ 錯誤寫法 (標準 SQL 子查詢，MySQL 不支援)： ```sql UPDATE materials m SET y_quantity = y_quantity + ( SELECT a.p_quantity FROM pro_a a WHERE a.p_name = m.y_name ) + ( SELECT b.p_quantity FROM pro_b b WHERE b.p_name = m.y_name );```

正確寫法：多表 JOIN UPDATE

-- 單表關聯累加
UPDATE materials m
LEFT JOIN pro_a a ON m.y_name = a.p_name
SET m.y_quantity = m.y_quantity + a.p_quantity;

-- 多表關聯累加 (依次 JOIN)
UPDATE materials m
LEFT JOIN pro_a a ON m.y_name = a.p_name
LEFT JOIN pro_b b ON m.y_name = b.p_name
SET m.y_quantity = m.y_quantity + IFNULL(a.p_quantity,0) + IFNULL(b.p_quantity,0);

語法結構

UPDATE table_a a
[INNER | LEFT | RIGHT] JOIN table_b b ON a.col = b.col
[INNER | LEFT | RIGHT] JOIN table_c c ON a.col = c.col
...
SET a.target_col = expression_involving(a.col, b.col, c.col...)
[WHERE condition];

關鍵點

• LEFT JOIN 保證主表所有行保留，無匹配時右表列為 NULL → 用 IFNULL/COALESCE 處理
• SET 子句可引用任意參與 JOIN 的表的列
• WHERE 可進一步過濾更新範圍
• MySQL 不支援 UPDATE ... FROM (SQL Server/PostgreSQL 語法)

進階：關聯聚合更新

若產品表有多行同名，需先聚合再更新：

UPDATE materials m
LEFT JOIN (
  SELECT p_name, SUM(p_quantity) AS total_qty FROM pro_a GROUP BY p_name
) a ON m.y_name = a.p_name
LEFT JOIN (
  SELECT p_name, SUM(p_quantity) AS total_qty FROM pro_b GROUP BY p_name
) b ON m.y_name = b.p_name
SET m.y_quantity = m.y_quantity + IFNULL(a.total_qty,0) + IFNULL(b.total_qty,0);

性能提示

• 關聯列 (y_name, p_name) 務必建索引
• 大表更新建議分批 (配合 LIMIT 或主鍵範圍)，避免長事務鎖表
• 可先用 SELECT 驗證 JOIN 結果：
  SELECT m.y_name, m.y_quantity, a.p_quantity, b.p_quantity FROM materials m LEFT JOIN ...