MySQL 多表 UPDATE 實戰大全：JOIN 更新、子查詢賦值與實戰模式

MySQL 多表更新是業務開發高頻操作。本文匯總 JOIN 更新、子查詢賦值、INNER JOIN 與逗號連接等價性、嵌套子查詢繞過限制等核心模式，附帶實戰代碼範例與性能建議。

一、 核心更新模式對比

二、 實戰範例詳解

1. 標準 JOIN 更新（最常用）

-- 同步文檔規則類型
UPDATE CMS_Document A
INNER JOIN CMS_Template B ON A.TemplateID = B.TemplateID
SET A.RuleType2 = B.RuleType;

等價寫法：

UPDATE CMS_Document A, CMS_Template B
SET A.RuleType2 = B.RuleType
WHERE A.TemplateID = B.TemplateID;

2. 多表多字段同步更新

UPDATE contact c
INNER JOIN contactdroit cd ON c.contact_id = cd.contact_id
SET 
    c.user_name = '$username',
    c.nom = '$lastname',
    c.prenom = '$firstname',
    c.passcode = '$password',
    cd.droit_id = '$droitid'
WHERE c.contact_id = '$id';

3. 關聯條件帶篩選

UPDATE Track
INNER JOIN MV ON Track.trkid = MV.mvid
SET Track.is_show = MV.is_show
WHERE Track.trkid < 6;

4. 業務邏輯更新：價格打折 + 更新時間

UPDATE product p
INNER JOIN productPrice pp ON p.productId = pp.productId
SET 
    pp.price = pp.price * 0.8,
    p.dateUpdate = CURDATE()
WHERE p.dateCreated < '2004-01-01';

5. 子查詢賦值（單值查找）

UPDATE customers a
SET city_name = COALESCE((
    SELECT b.city_name 
    FROM tmp_cust_city b 
    WHERE b.customer_id = a.customer_id
), '未知');

使用 COALESCE（或 NVL/IFNULL）提供默認值，防止子查詢返回 NULL 導致字段置空。

6. 同表更新繞過限制：臨時表包裹法

MySQL 不允許 UPDATE t SET col = (SELECT ... FROM t WHERE ...) 直接引用目標表。解法：

UPDATE apples
SET price = (
    SELECT price FROM (
        SELECT * FROM apples
    ) AS x
    WHERE variety = 'gala'
)
WHERE variety = 'fuji';

原理： 內層 SELECT * FROM apples 產生臨時表 x，外層查詢引用臨時表，繞過「目標表不可出現在 FROM 子句」限制。

7. 經典嵌套賦值模式

UPDATE mem_player 
SET DataWarehouse = (
    SELECT DataWarehouse FROM (
        SELECT * FROM mem_player
    ) AS b 
    WHERE Pid = 100000
);

三、 性能優化建議

1. 關聯字段加索引： ON / WHERE 條件列務必建立索引。
2. 避免大表全表掃描： 子查詢模式若無索引支援，會產生臨時表導致性能驟降。
3. 批量更新分批提交： 萬行級更新建議分批（如每 1000 行）提交，減少鎖持有時間與回滾段壓力。
4. 優先用 JOIN： 優化器對 JOIN 更新支援最成熟，可利用 EXPLAIN 驗證執行計劃。

四、 總結

MySQL 多表更新核心在於 JOIN 更新為主、子查詢為輔、臨時表繞過同表限制。掌握上述 7 種實戰模式，配合索引設計與分批提交，可高效應對各類業務同步、狀態流轉、數據修正場景。