This is on checking below link -
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:273215737113#followup-3016346200346884929 Below answer given by Tom -
"Consider, if T had:
OBJECT_NAME OBJECT_ID
-------------- ----------------
ABC xxxx
and T2 had:
OBJECT_NAME OBJECT_ID
---------------- ------------------
ABC 123
ABC 456
what should be the outcome of:
ADHOC@VOYAGER> update
2 (select t.object_id toid,t1.object_id t1oid
3 from t,t1
4 where t.object_name=t1.object_name
5 and upper(t.object_name)='PRIMARY_SALESFORCE')
6 set toid=t1oid;
should T have 123 or 456 and under what circumstances? Since the outcome of that update is 100% AMBIGOUS, we do not permit it. We need to ensure key preserved-ness and do that with a primary key/unique constraint.
You need that index anyway typically for performance."
This is the modification I have done to the query -
ADHOC@VOYAGER> update
2 (select t.object_id toid,t1.object_id t1oid
3 from t,t1
4 where t.object_name=t1.object_name
5 and upper(t.object_name)='PRIMARY_SALESFORCE')
6 set toid='test';
Still, I get the same error -
ORA-01779: cannot modify a column which maps to a non key-preserved table
What is the reason as I am trying to update it to 'test'