Oracle 更新語句避免子查詢無匹配導致 NULL：使用 WHERE EXISTS 或 MERGE

問題場景

-- t1 全量更新，t2 只有部分 id
UPDATE t1 a SET a.name = (SELECT b.name FROM t2 b WHERE a.id = b.id);
-- t1 中 id=3 在 t2 無對應 → name 變 NULL

方案一：WHERE EXISTS 過濾

UPDATE t1 a
SET a.name = (SELECT b.name FROM t2 b WHERE a.id = b.id)
WHERE EXISTS (SELECT 1 FROM t2 b WHERE a.id = b.id);
-- 僅更新 t2 存在的 id，其餘保持原值

方案二：MERGE 語句（Oracle 9i+，功能更強）

MERGE INTO t1 a
USING (SELECT id, name FROM t2) b
ON (a.id = b.id)
WHEN MATCHED THEN UPDATE SET a.name = b.name
-- 可選：WHEN NOT MATCHED THEN INSERT ...
;

方案三：LEFT JOIN 更新（Oracle 10g+ 支援 ANSI JOIN 更新）

UPDATE (
    SELECT a.name AS t1_name, b.name AS t2_name
    FROM t1 a LEFT JOIN t2 b ON a.id = b.id
)
SET t1_name = t2_name
WHERE t2_name IS NOT NULL;

效能比較

原答案核心

「update t1 A a set name= ( select b.name from t2 B where ID = a.ID ) where exists(select t2 1 from B where ID = a.ID )」