[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;
報錯:
解決方案
保證數據來源的唯一性,建立一個唯一索引即可。
建立索引後,再執行更新語法就正常了,圖為查詢結果:
參考資料
本著作係採用創用 CC 姓名標示-相同方式分享 4.0 國際 授權條款授權,文章歡迎轉載,請註明出處,謝謝~~~