Hello Guys - I am trying to make a view update-able but i am running into ORA-01779: cannot modify a column which maps to a non key-preserved table .
There are 2 tables: RESULTS and SHIPMASTER
RESULTS ( ship_nbr, status_code, .... ) -- other columns not relevant.
SHIPMASTER ( master_key, ship_nbr, status_code, user_id , ... ) -- other columns not relevant.
Current SELECT of the view.
WITH USER_LIST
AS (SELECT DISTINCT SHIP_NBR, USER_ID
FROM SHIPMASTER
WHERE STATUS_CODE = 10)
SELECT RESULTS.SHIP_NBR,
RESULTS.FLAG,
/* other details from RESULTS */
UPPER (USER_LIST.USER_ID)
FROM RESULTS, USER_LIST
WHERE RESULTS.STATUS_CODE = 10
AND RESULTS.SHIP_NBR = USER_LIST.SHIP_NBR
I want to run the following update statement :
update v_ship_user set flag = 'Y' where flag = 'N'
I tried removing the DISTINCT and using JOINS but still getting the ORA-1779
FACTS:
1. There is no primary key on RESULTS, the PK on SHIPMASTER is master_key
2. ship_nbr is not unique in either of the tables but each ship_nbr will have a distinct user_id associated with it.
( i.e selecting distinct ship_nbr, user_id will give me only 1 record per ship_nbr )
I read a few resources about using GTT, ( Tuning High-Volume SQL | Oracle FAQ )
but implementing it to run automatically beats me .