SQL 單一陳述式批次更新多筆資料：CASE WHEN 與 INSERT ... ON DUPLICATE KEY 兩大技巧

更新多筆資料且每筆欄位值不同，若在迴圈中逐筆 UPDATE 會產生大量資料庫往返。本文介紹兩種單一 SQL 陳述式完成批次更新的高效技巧。

場景範例

部落格分類表 categories：

需求：重新排序為「教程指南(1)、櫥窗展示(2)、免費資源(3)」，即更新三筆記錄的 display_order。

技巧一：CASE WHEN 條件式更新 (標準 SQL，跨資料庫支援)

UPDATE categories
SET display_order = CASE id
    WHEN 1 THEN 3   -- 免費資源 → 第 3 順位
    WHEN 2 THEN 1   -- 教程指南 → 第 1 順位
    WHEN 3 THEN 2   -- 櫥窗展示 → 第 2 順位
END,
title = CASE id
    WHEN 1 THEN '新標題1'
    WHEN 2 THEN '新標題2'
    WHEN 3 THEN '新標題3'
END
WHERE id IN (1, 2, 3);

程式語言動態生成範例

# PHP 範例
$display_order = [
    1 => 3,  // id => 新順序
    2 => 1,
    3 => 2,
];

$ids = implode(',', array_keys($display_order));
$sql = "UPDATE categories SET display_order = CASE id ";
foreach ($display_order as $id => $ordinal) {
    $sql .= sprintf("WHEN %d THEN %d ", $id, $ordinal);
}
$sql .= "END WHERE id IN ($ids)";

echo $sql;  // 除錯輸出
mysqli_query($conn, $sql);

優點：單次網路往返、標準 SQL 相容性佳、可同時更新多欄位。
注意：SQL 字串長度限制 (PHP 通常支援 >1MB)，超大批次 (10萬筆+) 需分批處理。

技巧二：INSERT ... ON DUPLICATE KEY UPDATE (MySQL 專用)

利用主鍵衝突觸發更新，適用「不存在則插入，存在則更新」 (Upsert) 場景：

INSERT INTO t_member (id, name, email) VALUES
(1, 'nick', 'nick@126.com'),
(4, 'angel', 'angel@163.com'),
(7, 'brank', 'ba198@126.com')
ON DUPLICATE KEY UPDATE 
    name = VALUES(name), 
    email = VALUES(email);

• VALUES(col) 參照 VALUES 子句中該欄位的輸入值
• 限制：僅 MySQL/MariaDB 支援，非標準 SQL
• SQL Server 替代：MERGE 陳述式 (2008+) 或 UPSERT 模式

SQL Server 對應寫法：MERGE 陳述式

-- 來源資料 (可來自 Table 變數、臨時表、TVP)
DECLARE @src TABLE (id INT PRIMARY KEY, display_order INT, title NVARCHAR(50));
INSERT @src VALUES (1,3,'新標題1'), (2,1,'新標題2'), (3,2,'新標題3');

MERGE categories AS tgt
USING @src AS src ON tgt.id = src.id
WHEN MATCHED THEN
    UPDATE SET display_order = src.display_order, title = src.title
WHEN NOT MATCHED THEN
    INSERT (id, display_order, title) VALUES (src.id, src.display_order, src.title);

優點：標準 SQL、單一陳述式完成增刪改、可處理複雜邏輯。

效能對比與選擇建議

最佳實務建議

1. 批次大小控制在 1000-5000 筆，避免單一 SQL 過大導致編譯超時或計劃緩存污染
2. 使用 Table-Valued Parameter (TVP) 傳遞批次資料給 SP，型別安全且效能最佳
3. 更新前先 BEGIN TRAN，確保原子性；大批次考慮分批提交避免長鎖定
4. 配合 OUTPUT 子句記錄變更明細供稽核