Oracle UPDATE ... WITH 寫法實戰：分析函數賦值避免臨時表

Oracle UPDATE ... WITH 寫法實戰：分析函數賦值避免臨時表

需求：CODE 欄位有重複值，依 CODE 分組加流水號（如 201402_1、201402_2）。傳統寫法需建臨時表或 MERGE，Oracle 支援 UPDATE ... SET col = (WITH ... SELECT ...) 直接完成。

測試資料

DROP TABLE test PURGE;
CREATE TABLE test (id NUMBER, code VARCHAR2(20), name VARCHAR2(20));
INSERT INTO test VALUES (1,'201401','aaa');
INSERT INTO test VALUES (2,'201402','bbb');
INSERT INTO test VALUES (3,'201402','ccc');
INSERT INTO test VALUES (4,'201403','ddd');
INSERT INTO test VALUES (5,'201403','eee');
INSERT INTO test VALUES (6,'201403','fff');
COMMIT;

1. 分析函數產生目標值

SELECT t.id, 
       code || '_' || ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS new_code
FROM test t;

-- 結果：
-- 1  201401_1
-- 2  201402_1
-- 3  201402_2
-- 4  201403_1
-- 5  201403_2
-- 6  201403_3

2. 傳統子查詢寫法（失敗）

UPDATE test t 
SET t.code = (
  SELECT code || '_' || ROW_NUMBER() OVER (PARTITION BY code ORDER BY id)
  FROM test t1 WHERE t1.id = t.id
);
-- 結果錯誤：所有 201402 變 201402_1，201403 變 201403_1
-- 原因：標量子查詢每行獨立執行，窗口函數無法跨行分區

3. UPDATE + WITH 正確寫法

UPDATE test b 
SET b.code = (
  WITH t AS (
    SELECT id, code || '_' || ROW_NUMBER() OVER (PARTITION BY code ORDER BY id) AS new_code
    FROM test
  )
  SELECT a.new_code FROM t a WHERE a.id = b.id
);

-- 結果正確：
-- 1  201401_1  aaa
-- 2  201402_1  bbb
-- 3  201402_2  ccc
-- 4  201403_1  ddd
-- 5  201403_2  eee
-- 6  201403_3  fff

原理解析

• WITH 子查詢（CTE）在 UPDATE 陳述式層級執行**一次**，產生完整結果集
• 外層關聯 WHERE a.id = b.id 逐行映射，窗口函數正確分區計算
• 避免了建立臨時表、MERGE 或 PL/SQL 循環

適用場景

1. 更新值依賴同表其他行的聚合/排序/分析函數結果
2. 多欄位同時更新，來源為同一複雜查詢
3. 避免 Context Switch，單一 SQL 完成

注意事項

• Oracle 12c+ 也可用 MERGE 搭配 WITH 更直觀
• 大量資料更新建議分批 WHERE ROWNUM <= 10000 循環，避免 Undo 爆滿
• 更新前備份或可閃回 FLASHBACK TABLE test TO BEFORE DROP