Oracle 多表關聯 UPDATE 實戰語法大全

場景背景

業務系統中常需根據另一張表（或多張表）的資料更新目標表欄位。本文以「客戶資料表 customers」與「臨時修正表 tmp_cust_city」為例，展示三種常見多表 UPDATE 寫法。

資料模型

-- 目標表
CREATE TABLE customers (
  customer_id NUMBER(8) PRIMARY KEY,
  city_name   VARCHAR2(10),
  customer_type CHAR(2)
);

-- 來源修正表
CREATE TABLE tmp_cust_city (
  customer_id NUMBER(8) PRIMARY KEY,
  city_name   VARCHAR2(10),
  customer_type CHAR(2)
);

案例 1：單表條件更新（最簡單）

將 customer_id < 1000 的所有客戶城市設為「北京」。

UPDATE customers
SET city_name = '北京'
WHERE customer_id < 1000;

案例 2：多表關聯，僅在 WHERE 子句 JOIN（更新固定值）

針對存在於 tmp_cust_city 的客戶，將類型更新為 VIP (01)。

UPDATE customers a
SET customer_type = '01'
WHERE EXISTS (
  SELECT 1
  FROM tmp_cust_city b
  WHERE b.customer_id = a.customer_id
);

優點：語意清晰、效能佳（半連接），來源表唯一鍵可避免重複更新。

案例 3：多表關聯，SET 子句引用來源表欄位（動態值）

根據 tmp_cust_city 的城市名稱更新 customers。

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
);

注意：子查詢必須保證 單一行回傳，否則拋出 ORA-01427。來源表 customer_id 應有唯一索引/主鍵。

進階寫法：MERGE 語句 (Oracle 9i+ 推薦)

單一語句完成「有則更新、無則插入」，語法更完整、效能更穩定：

MERGE INTO customers a
USING tmp_cust_city b
ON (a.customer_id = b.customer_id)
WHEN MATCHED THEN
  UPDATE SET
    a.city_name = b.city_name,
    a.customer_type = b.customer_type;

效能與避坑指南

• 索引：連接欄位 (customer_id) 兩側皆需索引。
• 大量更新：考慮分批次 COMMIT、使用 NOLOGGING 臨時表、或 PARALLEL DML。
• 避免相關子查詢掃描全表：確保 WHERE EXISTS 子查詢能用到索引範圍掃描。
• 鎖爭用：長時間更新易造成行鎖等待，建議在低峰期執行或鎖定表統計資訊。