MySQL 多表 UPDATE 實戰詳解：JOIN 更新、子查詢、NVL 處理與實戰案例

核心語法：JOIN 更新（推薦）

MySQL 支持在 UPDATE 中直接使用 JOIN，語法清晰、性能優於子查詢：

UPDATE table1 t1
INNER JOIN table2 t2 ON t1.join_col = t2.join_col
SET t1.target_col = t2.source_col
WHERE t1.filter_col = 'value';

經典實戰案例

案例 1：模板規則同步

UPDATE CMS_Document A
INNER JOIN CMS_Template B ON A.TemplateID = B.TemplateID
SET A.RuleType2 = B.RuleType;

案例 2：價格批量調整（帶條件）

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';

案例 3：聯繫人與權限同步更新

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';

子查詢更新與 NULL 值處理

子查詢更新需確保返回單行，且常需處理無匹配行導致的 NULL：

-- 子查詢寫法（性能較差）
UPDATE tab1
SET tab1.商品尺寸 = (
    SELECT tab2.商品尺寸 
    FROM tab2 
    WHERE tab2.商品顏色 = tab1.商品顏色
)
WHERE tab1.商品顏色 IN (SELECT 商品顏色 FROM tab2);

-- 使用 NVL/COALESCE 設置默認值（Oracle/PostgreSQL/SQL Server 語法）
-- MySQL 使用 COALESCE() 或 IFNULL()
UPDATE customers a
SET city_name = COALESCE((
    SELECT b.city_name 
    FROM tmp_cust_city b 
    WHERE b.customer_id = a.customer_id
), '未知');

性能優化建議

• 首選 JOIN 更新： 優化器能更好利用索引，避免重複掃描。
• 確保 JOIN 條件列有索引： 尤其被驅動表的關聯列。
• 大量更新分批： 配合 `LIMIT` 或主鍵範圍分批，控制事務大小。
• 避免更新索引列： 會導致索引維護開銷大增。

遷移提示

PHP 中使用預處理：

$sql = "UPDATE product p
        INNER JOIN productPrice pp ON p.productId = pp.productId
        SET pp.price = pp.price * ?, p.dateUpdate = CURDATE()
        WHERE p.dateCreated < ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([0.8, '2004-01-01']);