SQL UPDATE 多條件 WHERE 子句：IN/EXISTS/JOIN 三種寫法對比

需求：根據臨時表 TempInfo 匹配 Student 表條件，更新 Result 表中符合 雙字段組合 (SNo, CNo) 的記錄。單字段 IN/ANY 易誤匹配，需多字段關聯更新。

錯誤嘗試

-- ❌ 語法錯誤：標準 SQL 不支援 (col1, col2) = (subquery)
UPDATE Result SET A = A+1 
WHERE (Result.SNo, Result.CNo) = ALL (
    SELECT TempInfo.SNo, TempInfo.CNo 
    FROM Student, TempInfo 
    WHERE Student.SNo=TempInfo.SNo AND Student.BtAddress=TempInfo.BtAddress
);

方案一：EXISTS 相關子查詢 (標準、可讀、效能好)

UPDATE Result 
SET A = A + 1
WHERE EXISTS (
    SELECT 1 
    FROM Student s
    JOIN TempInfo t ON s.SNo = t.SNo AND s.BtAddress = t.BtAddress
    WHERE Result.SNo = t.SNo AND Result.CNo = t.CNo
);

方案二：IN + 構造複合鍵字符串 (兼容性好、需注意分隔符衝突)

UPDATE Result 
SET A = A + 1
WHERE CONCAT(SNo, '#', CNo) IN (
    SELECT CONCAT(TempInfo.SNo, '#', TempInfo.CNo)
    FROM Student, TempInfo
    WHERE Student.SNo = TempInfo.SNo AND Student.BtAddress = TempInfo.BtAddress
);
-- 或使用標準 SQL 行構造器 (PostgreSQL, Oracle 12c+, SQL Server 2008+)
WHERE (SNo, CNo) IN (
    SELECT TempInfo.SNo, TempInfo.CNo
    FROM Student JOIN TempInfo ON Student.SNo = TempInfo.SNo AND Student.BtAddress = TempInfo.BtAddress
);

方案三：JOIN 更新 (MySQL/SQL Server/PostgreSQL 語法略異)

-- MySQL / PostgreSQL
UPDATE Result r
JOIN (
    SELECT t.SNo, t.CNo
    FROM Student s
    JOIN TempInfo t ON s.SNo = t.SNo AND s.BtAddress = t.BtAddress
) tmp ON r.SNo = tmp.SNo AND r.CNo = tmp.CNo
SET r.A = r.A + 1;

-- SQL Server (FROM 子句)
UPDATE r
SET r.A = r.A + 1
FROM Result r
JOIN (
    SELECT t.SNo, t.CNo
    FROM Student s
    JOIN TempInfo t ON s.SNo = t.SNo AND s.BtAddress = t.BtAddress
) tmp ON r.SNo = tmp.SNo AND r.CNo = tmp.CNo;

方案四：原問題雙 ANY 邏輯 (邏輯等價但效能差)

UPDATE Result 
SET A = A + 1
WHERE SNo IN (
    SELECT TempInfo.SNo FROM Student, TempInfo 
    WHERE Student.SNo=TempInfo.SNo AND Student.BtAddress=TempInfo.BtAddress
)
AND CNo IN (
    SELECT TempInfo.CNo FROM Student, TempInfo 
    WHERE Student.SNo=TempInfo.SNo AND Student.BtAddress=TempInfo.BtAddress
);
-- ⚠️ 風險：SNo 和 CNo 獨立匹配，可能匹配到不同行的組合 (笛卡爾積風險)

效能與適用性對比

最佳實踐建議

1. 首選 EXISTS：語義清晰「存在即更新」、標準 SQL、優化器友好、自動短路。
2. 加索引：Result(SNo, CNo)、TempInfo(SNo, CNo)、Student(SNo, BtAddress) 複合索引。
3. 避免 SELECT *：子查詢只 SELECT 1 或具體列。
4. 大數據量分批更新：WHERE EXISTS ... AND ROWNUM <= 10000 循環提交，避免長事務鎖表。
5. 測試先行：先跑 SELECT * FROM Result WHERE EXISTS ... 確認影響行數。

核心原則：多字段組合匹配用 EXISTS 相關子查詢，既標準又高效，避開 IN 子查詢的獨立匹配陷阱與方言差異。