UPDATE 是修改資料的核心 DML，支援單表、多表、排序、限制行數，但細節極多。
單表語法
UPDATE [LOW_PRIORITY] [IGNORE] tbl_name
SET col1 = expr1 [, col2 = expr2 ...]
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]
多表語法
UPDATE [LOW_PRIORITY] [IGNORE] table_references
SET col1 = expr1 [, col2 = expr2 ...]
[WHERE where_condition]
-- 不支援 ORDER BY、LIMIT
關鍵修飾符
| 修飾符 | 行為 |
|---|
| LOW_PRIORITY | 延遲執行，直到無其他連線讀取該表 |
| IGNORE | 遇到重複鍵衝突/資料轉換錯誤**跳過該行繼續**，不中斷 |
賦值順序與陷阱
-- 從左到右依序賦值
UPDATE t SET a = a + 1, a = a * 2; -- 先 +1 再 *2
-- 子查詢引用同表 (MySQL 不支援)
UPDATE t SET a = (SELECT MAX(a) FROM t); -- ERROR 1093
-- 改用多表 UPDATE
UPDATE t JOIN (SELECT MAX(a) AS m FROM t) t2 SET t.a = t2.m;
多表 UPDATE 實戰
-- 同步價格表
UPDATE items i
JOIN month_price m ON i.id = m.item_id
SET i.price = m.price;
-- 支援 LEFT JOIN (僅更新有匹配的行)
UPDATE items i
LEFT JOIN month_price m ON i.id = m.item_id
SET i.price = COALESCE(m.price, i.price);
常見錯誤與解決
| 錯誤 | 原因 | 解決 |
|---|
| ERROR 1093 | UPDATE 同表且子查詢引用該表 | 改用 JOIN 衍生表 |
| ERROR 1175 (Safe Update Mode) | 無 WHERE 或無索引條件 | SET SQL_SAFE_UPDATES=0; 或加索引 WHERE |
| 更新行數為 0 | WHERE 條件不匹配 / 型別隱含轉型 | 檢查參數型別、用 CAST |
| 外鍵約束失敗 | 更新父表主鍵 / 刪除被引用行 | 先更新子表 / 設定 ON UPDATE CASCADE |
效能建議
- **WHERE 條件必須命中索引**，避免全表掃描鎖表
- 大批次更新 **分批 COMMIT** (每 1000~5000 行)，避免長事務、Undo 膨脹、主從延遲
LOW_PRIORITY 適合離線維護窗口，線上業務慎用
