Oracle僅更新兩表對應資料的兩種高效寫法與WHERE陷阱

場景建立

-- 來源表 (含更新資料)
CREATE TABLE emp1 AS SELECT * FROM emp WHERE deptno = 20;

-- 模擬更新
UPDATE emp1 SET sal = sal + 100, comm = NVL(comm,0) + 50;

-- 目標表 emp 需同步 emp1 的 sal、comm

寫法一：子查詢 + 關鍵WHERE條件 (標準SQL)

UPDATE emp
SET (sal, comm) = (SELECT sal, comm FROM emp1 WHERE emp1.empno = emp.empno)
WHERE EXISTS (SELECT 1 FROM emp1 WHERE emp1.empno = emp.empno);

⚠ 致命陷阱：省略WHERE EXISTS子句會導致所有不匹配行的sal/comm變為NULL！Oracle UPDATE預設更新所有行，子查詢無匹配時回傳NULL而非「跳過」。

寫法二：可更新視圖/內聯視圖 (Oracle特有)

UPDATE (
  SELECT a.sal AS asal, b.sal AS bsal,
         a.comm AS acomm, b.comm AS bcomm
  FROM emp a, emp1 b
  WHERE a.empno = b.empno
)
SET asal = bsal, acomm = bcomm;

前置條件：目標表必須有主鍵/唯一索引

若emp1無主鍵，執行寫法二會報錯：

ORA-01779: cannot modify a column which maps to a non key-preserved table

解決：

ALTER TABLE emp1 ADD CONSTRAINT pk_emp1 PRIMARY KEY (empno);

兩種寫法比較

總結建議

1. 永遠加上WHERE EXISTS (或WHERE empno IN (SELECT empno FROM emp1)) 防止誤更新為NULL
2. 目標表有PK/UK時，可更新視圖法更優雅且自帶保護
3. 大量資料考慮MERGE語句 (10g+)：MERGE INTO emp USING emp1 ON (emp.empno=emp1.empno) WHEN MATCHED THEN UPDATE SET sal=emp1.sal, comm=emp1.comm