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

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

錯誤概述

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

 

錯誤訊息

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;

 

報錯:

image

 

解決方案

保證數據來源的唯一性,建立一個唯一索引即可。

 



  

 

建立索引後,再執行更新語法就正常了,圖為查詢結果:

image

 

 

參考資料

ORA-01779的處理方式

 

創用 CC 授權條款
本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權,文章歡迎轉載,請註明出處,謝謝~~~