MySQL 跨表 UPDATE 寫法大全：單欄、多欄、子查詢與 JOIN 優化

MySQL 支援多種跨表更新語法，效能差異巨大。本文從基礎寫法到 JOIN 優化，透過實例對比展示最高效的跨表 UPDATE 實作模式。

一、基礎跨表更新語法

1. 單欄更新 (隱式 JOIN)

UPDATE student s, city c
SET s.city_name = c.name
WHERE s.city_code = c.code;

2. 多欄更新

UPDATE a, b
SET a.title = b.title, a.name = b.name
WHERE a.id = b.id;

3. 子查詢寫法 (效能較差)

UPDATE student s
SET city_name = (SELECT name FROM city WHERE code = s.city_code);

二、實戰案例：訂單狀態同步優化

低效寫法：重複子查詢

UPDATE t_advs_order
SET attribute1 = (SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id = `on`),
    attribute2 = (SELECT o.order_state FROM tk_zyt_scenery_order o WHERE o.order_id = `on`)
WHERE EXISTS (SELECT 1 FROM tk_zyt_scenery_order o WHERE o.order_id = `on`);

問題： 三次掃描同一張表，隨資料量增長效能指數級下降。

高效寫法：INNER JOIN

UPDATE t_advs_order a
INNER JOIN tk_zyt_scenery_order s ON s.order_id = a.`on`
SET a.attribute1 = s.order_id, a.attribute2 = s.order_id;

優勢： 單次 JOIN 完成多欄更新，利用索引關聯，效能提升數十倍。

三、跨表 UPDATE 進階模式

LEFT JOIN 保留左表所有記錄

UPDATE t1
LEFT JOIN t2 ON t1.id = t2.t1_id
SET t1.col = COALESCE(t2.value, 'default');

多表關聯更新

UPDATE t1
JOIN t2 ON t1.id = t2.t1_id
JOIN t3 ON t2.id = t3.t2_id
SET t1.status = t3.final_status
WHERE t1.type = 'order';

條件更新 (CASE WHEN)

UPDATE orders o
JOIN customers c ON o.cust_id = c.id
SET o.discount = CASE
    WHEN c.vip_level = 3 THEN 0.8
    WHEN c.vip_level = 2 THEN 0.9
    ELSE 1.0
END;

四、效能優化關鍵點

1. 關聯欄位必須建立索引 (JOIN ON 條件欄位)
2. 優先使用 INNER JOIN 而非子查詢
3. 避免在 SET 子句中使用子查詢
4. 大量更新時分批處理 (配合 LIMIT 或主鍵範圍)
5. 更新前先用 SELECT 驗證影響範圍

五、MySQL 8.0+ 新特性：CTE 輔助更新

WITH updated AS (
    SELECT o.id, c.new_status
    FROM orders o
    JOIN customers c ON o.cust_id = c.id
    WHERE o.status = 'pending'
)
UPDATE orders o
JOIN updated u ON o.id = u.id
SET o.status = u.new_status;