ORA-01779: cannot modify a column which maps to a non key-preserved table on updating joined tables

Question

I have this query.

update 
(
select pt.poid_id0, umt.plan_id
from norie.plan_t_test pt 
join norie.UAS_MAPPING_TEST umt on (pt.poid_id0 = umt.plan_id)
)
set plan_id = poid_id0
where poid_id0 <> plan_id;

but ORA-01779 displays. Please help.

Answer 1

Updating a query is always tricky. Try a merge instead.

MERGE INTO uas_mapping_test t1
USING 
(   
  select pt.poid_id0, umt.plan_id
    from norie.plan_t_test pt 
    join norie.UAS_MAPPING_TEST umt on (pt.poid_id0 = umt.plan_id)
) ta ON poid_id0 <> plan_id
WHEN MATCHED THEN UPDATE 
set plan_id = poid_id0