Oracle 關聯更新（UPDATE ... SELECT）完全指南：WHERE 子句決定生死、五大寫法性能大比拼

核心鐵律：WHERE 子句是靈魂

Oracle 11g+ 中，**關聯更新若無 WHERE 限制，不匹配行會被置為 NULL**（而非保持原值）。
必須雙重保障：
1. 子查詢返回**單行**（否則 ORA-01427）。
2. 外層 WHERE 精確限定**待更新行集合**，與子查詢匹配集合**完全一致**。

五大標準寫法對比

寫法 1：標量子查詢 + 外層 WHERE EXISTS（推薦通用）

UPDATE sfism4.r_dt_dip_traceability b
SET (b.current_qty, b.update_time, b.update_user) = (
  SELECT DECODE(SIGN(b.current_qty - a.kp_count), -1, 0, b.current_qty - a.kp_count),
         SYSDATE, :vuser
  FROM sfism1.c_dt_dip_bom_t a, sfism4.r_mo_base_t c
  WHERE a.bom_no = b.bom_no
    AND b.bom_no = c.key_part_no
    AND a.key_part_no = b.key_part_no
    AND c.mo_number = :vbom
    AND b.trac_lot = :vtracelot
)
WHERE b.trac_lot = :vtracelot
  AND EXISTS (
    SELECT 1 FROM sfism1.c_dt_dip_bom_t aa
    WHERE b.bom_no = aa.bom_no
      AND b.key_part_no = aa.key_part_no
  );

✅ 子查詢關聯外層表 b，外層 WHERE 精確鎖定目標行，EXISTS 確保匹配存在。

寫法 2：標量子查詢 + 外層 WHERE IN

UPDATE t_target a
SET a.col = (SELECT b.val FROM t_source b WHERE b.id = a.id)
WHERE a.id IN (SELECT id FROM t_source);

⚠️ IN 子查詢結果集含 NULL 會導致整體 UNKNOWN，需確保源表 ID 非空。

寫法 3：UPDATE 視圖（需主鍵保護）

UPDATE (
  SELECT a.town AS a_town, b.town AS b_town
  FROM wwm2 a, wwm5 b
  WHERE a.id = b.id
) SET a_town = b_town;

⚠️ 要求**被更新表有主鍵/唯一約束**，且連接鍵包含該鍵，否則 ORA-01779。

寫法 4：MERGE INTO（10g+ 標準化、支持 INSERT 分支）

MERGE INTO wwm2 t
USING (SELECT id, town FROM wwm5) s
ON (t.id = s.id)
WHEN MATCHED THEN UPDATE SET t.town = s.town
-- WHEN NOT MATCHED THEN INSERT ...  -- 可選
;

✅ 語義清晰、性能穩健、支持 WHEN NOT MATCHED 同步新增，**首選方案**。

寫法 5：PL/SQL 遊標循環（萬金油、靈活但慢）

DECLARE
  CURSOR c IS SELECT id, town FROM wwm5;
BEGIN
  FOR r IN c LOOP
    UPDATE wwm2 SET town = r.town WHERE id = r.id;
  END LOOP;
  COMMIT;
END;

⚠️ 行級提交/上下文切換開銷大，百萬級數據慎用；適合複雜邏輯、需中間處理的場景。

性能實測（10 萬行目標表，5 萬行源表）

避坑檢查清單

1. [ ] 子查詢是否**保證單行**？（加 ROWNUM=1 或唯一索引保障）
2. [ ] 外層 WHERE 是否**精確等於**子查詢匹配集？
3. [ ] 目標表是否有**主鍵/唯一約束**？（UPDATE 視圖/MERGE 需求）
4. [ ] 是否需要 **WHEN NOT MATCHED** 同步新增？→ 選 MERGE。
5. [ ] 大數據量是否啟用 **Parallel DML**？ALTER SESSION ENABLE PARALLEL DML + UPDATE /*+ PARALLEL(4) */。
6. [ ] 統計信息是否最新？（關聯更新極依賴基數估算）

一句話總結

關聯更新核心是雙重 WHERE 保護；首選 MERGE，次選標量子查詢+EXISTS；大數據量上並行 DML；統計信息要鮮活。