Oracle UPDATE 語法全解析：多表關聯、效能優化與常見陷阱

UPDATE 核心語法變體

場景設定

-- 目標表：客戶主檔
CREATE TABLE CUSTOMERS (
  CUSTOMER_ID NUMBER(8) PRIMARY KEY,
  CITY_NAME VARCHAR2(10),
  CUSTOMER_TYPE CHAR(2)
);

-- 來源表：CRM 修正資料 (20% 覆蓋率)
CREATE TABLE TMP_CUST_CITY (
  CUSTOMER_ID NUMBER(8) PRIMARY KEY,
  CITY_NAME VARCHAR2(10),
  CUSTOMER_TYPE CHAR(2)
);

1. 單表簡單更新

UPDATE CUSTOMERS 
SET CITY_NAME = '北京' 
WHERE CUSTOMER_ID < 1000;

2. 多表關聯更新 - 僅 EXISTS 判斷

UPDATE CUSTOMERS A
SET CUSTOMER_TYPE = '01'  -- VIP
WHERE EXISTS (
  SELECT 1 FROM TMP_CUST_CITY B 
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
);

3. 多表關聯更新 - 來源表提供新值

-- 單欄更新
UPDATE CUSTOMERS A
SET CITY_NAME = (
  SELECT B.CITY_NAME FROM TMP_CUST_CITY B 
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
)
WHERE EXISTS (
  SELECT 1 FROM TMP_CUST_CITY B 
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
);

-- 多欄同時更新 (語法關鍵：括號包裹欄位清單與子查詢)
UPDATE CUSTOMERS A
SET (CITY_NAME, CUSTOMER_TYPE) = (
  SELECT B.CITY_NAME, B.CUSTOMER_TYPE 
  FROM TMP_CUST_CITY B 
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
)
WHERE EXISTS (
  SELECT 1 FROM TMP_CUST_CITY B 
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
);

4. NVL 防禦性寫法 - 處理來源資料不全

UPDATE CUSTOMERS A
SET CITY_NAME = NVL((
  SELECT B.CITY_NAME FROM TMP_CUST_CITY B 
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
), A.CITY_NAME);  -- 無匹配保持原值

-- 或指定預設值
SET CITY_NAME = NVL((...), '未知');

效能優化：游標迴圈 vs 集合操作

當來源表資料量遠小於目標表 (如 20-30%)，且目標表有索引時，PL/SQL 迴圈可能優於集合 UPDATE：

DECLARE
  CURSOR CITY_CUR IS
    SELECT CUSTOMER_ID, CITY_NAME 
    FROM TMP_CUST_CITY 
    ORDER BY CUSTOMER_ID;
BEGIN
  FOR REC IN CITY_CUR LOOP
    UPDATE CUSTOMERS
    SET CITY_NAME = REC.CITY_NAME
    WHERE CUSTOMER_ID = REC.CUSTOMER_ID;
    
    -- 批次提交避免長交易鎖擴大
    IF MOD(CITY_CUR%ROWCOUNT, 10000) = 0 THEN
      COMMIT;
    END IF;
  END LOOP;
  COMMIT;
END;
/

優勢：每次單行索引查找，避免全表掃描；劣勢：Context Switch 開銷、大量提交增加 Redo。

進階技巧：MERGE 風格 UPDATE (視圖更新法)

UPDATE (
  SELECT A.CITY_NAME AS OLD_CITY, B.CITY_NAME AS NEW_CITY
  FROM CUSTOMERS A, TMP_CUST_CITY B
  WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
)
SET OLD_CITY = NEW_CITY;

前提條件：聯結鍵必須是目標表主鍵/唯一鍵 (Key-Preserved)，否則 ORA-01779。

效能優勢：單次掃描來源表，避免子查詢重複存取。

常見錯誤與解決方案

ORA-01407: Cannot update to NULL

原因：子查詢無匹配行返回 NULL，目標欄位 NOT NULL。

解決：加 WHERE EXISTS 條件或使用 NVL/COALESCE。

ORA-01427: Single-row subquery returns more than one row

原因：來源表聯結鍵非唯一，單一目標行匹配多筆來源行。

排查查詢：

SELECT B.CUSTOMER_ID, B.CITY_NAME, COUNT(*)
FROM TMP_CUST_CITY B, CUSTOMERS A
WHERE B.CUSTOMER_ID = A.CUSTOMER_ID
GROUP BY B.CUSTOMER_ID, B.CITY_NAME
HAVING COUNT(*) > 1;

解決：來源表去重、加聚合邏輯、或業務確認取最新/優先值。

ORA-01779: Cannot modify non key-preserved table

原因：視圖更新法中目標表非 Key-Preserved。

解決：確保聯結條件包含目標表主鍵/唯一鍵，或改用標準 UPDATE 子查詢。

效能對比總結表