UPDATE子查詢的ORA-1779錯誤

在對子查詢執行UPDATE時，可能會遇到ORA-1779錯誤，這裡簡單討論一下。

 

 

首先構造兩個表：

SQL> CREATE TABLE T1 (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30), DOC VARCHAR2(30));

表已建立。

SQL> CREATE TABLE T2 (ID NUMBER PRIMARY KEY, FID NUMBER, NAME VARCHAR2(30), DOC VARCHAR2(30));

表已建立。

SQL> INSERT INTO T1 VALUES (1, 'A', 'TEST1');

已建立 1 行。

SQL> INSERT INTO T1 VALUES (2, 'B', 'TEST2');

已建立 1 行。

SQL> INSERT INTO T1 VALUES (3, 'C', 'TEST3');

已建立 1 行。

SQL> INSERT INTO T2 VALUES (1, 1, 'A', 'TESTA');

已建立 1 行。

SQL> INSERT INTO T2 VALUES (2, 2, 'B', 'TESTB');

已建立 1 行。

SQL> INSERT INTO T2 VALUES (3, 3, 'C', 'TESTC');

已建立 1 行。

SQL> INSERT INTO T2 VALUES (4, NULL, 'A', 'TESTD');

已建立 1 行。

SQL> COMMIT;

提交完成。

經常有需求會根據一張表的記錄更新另一張表上對於記錄的欄位，一般這個SQL如下所示：

SQL> UPDATE T2 SET DOC = (SELECT DOC FROM T1 WHERE ID = T2.FID)
  2  WHERE EXISTS (SELECT 1 FROM T1 WHERE ID = T2.FID);

已更新3行。

SQL> SELECT * FROM T2;

        ID        FID NAME                           DOC
---------- ---------- ------------------------------ ---------------------
         1          1 A                              TEST1
         2          2 B                              TEST2
         3          3 C                              TEST3
         4            A                              TESTD

SQL> ROLLBACK;

回退已完成。

其中WHERE條件是為了避免將連線中匹配不到的記錄更新為NULL。

與此等價的有另外一種寫法，直接對子查詢進行更新：

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.FID)
  2  SET DOC_2 = DOC_1;

已更新3行。

SQL> SELECT * FROM T2;

        ID        FID NAME                           DOC
---------- ---------- ------------------------------ ---------------------------
         1          1 A                              TEST1
         2          2 B                              TEST2
         3          3 C                              TEST3
         4            A                              TESTD

SQL> ROLLBACK;

回退已完成。

採用這種方式，SQL顯得更加簡潔。

不過這種方式使用不當就會造成ORA-1779錯誤：

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.FID)
  2  SET DOC_1 = DOC_2;
SET DOC_1 = DOC_2
    *
第 2 行出現錯誤:
ORA-01779: 無法修改與非鍵值儲存表對應的列

造成這個錯誤的原因是更新的列不是事實表的列，而是維度表的列。換句話說，如果兩張表關聯，其中一張表的關聯列是主鍵，那麼另一張表就是事實表，也就是說另一張表中的列就是可更新的；除非另一張表的關聯列也是主鍵，否則這張表就是不可更新的，如果更新語句涉及到了這張表，就會出現ORA-1799錯誤。

如果是兩張表主鍵關聯，那麼無論更新那個表的欄位都可以：

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.ID)
  2  SET DOC_1 = DOC_2;

已更新3行。

SQL> ROLLBACK;

回退已完成。

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.ID = T2.ID)
  2  SET DOC_2 = DOC_1;

已更新3行。

SQL> ROLLBACK;

回退已完成。

如果兩張表都不包含主鍵，那麼無論更新那張表的欄位都會報錯：

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.NAME = T2.NAME)
  2  SET DOC_1 = DOC_2;
SET DOC_1 = DOC_2
    *
第 2 行出現錯誤:
ORA-01779: 無法修改與非鍵值儲存表對應的列

SQL> UPDATE (SELECT T1.DOC DOC_1, T2.DOC DOC_2 FROM T1, T2 WHERE T1.NAME = T2.NAME)
  2  SET DOC_2 = DOC_1;
SET DOC_2 = DOC_1
    *
第 2 行出現錯誤:
ORA-01779: 無法修改與非鍵值儲存表對應的列

其實這個限制的真正原因是Oracle要確保連線後更新的內容可以寫到一張表中，而這就要求連線方式必須是1對N或者1對1的連線。這樣才能確保連線後的結果集數量和事實表一致。從而使得Oracle對連線後子查詢的更新可以順利的更新到事實表中。