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

2015-07-16

1718
0
Oracle
2017-09-05

錯誤概述

無法修改對映非保留鍵表的資料欄。

錯誤訊息

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 - "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

情境說明

下 UPDATE 語法時出錯，檢查來源資料表也沒有 1 對多或多對1 的重複情況

情境模擬語法:


CREATE TABLE TABLE_SOURCE(
  ID NUMBER,
  NAME VARCHAR2(20)
);

--建立更新表
CREATE TABLE TABLE_TARGET(
  ID NUMBER,
  NAME VARCHAR2(20)
);

--寫入資料
INSERT INTO TABLE_SOURCE(ID, NAME) VALUES(1, 'S_A');
INSERT INTO TABLE_SOURCE(ID, NAME) VALUES(2, 'S_B');
INSERT INTO TABLE_TARGET(ID, NAME) VALUES(1, 'A');
INSERT INTO TABLE_TARGET(ID, NAME) VALUES(2, 'B');

--更新
UPDATE (
  SELECT 
    A.NAME AS S_COL1, 
    B.NAME AS T_COL1
  FROM TABLE_SOURCE A, TABLE_TARGET B
  WHERE A.ID = B.ID
) SET T_COL1 = S_COL1;

報錯: