Transform update statement with joins from SQL Server into Oracle
This update statement works perfectly on SQL Server, but on Oracle doesn’t.
UPDATE wfu set klient_id_crma= K.KLIENT_PROD_ID , umowa_id_crma= u.umowa_id from wh_ods.umowy u join WNIOSKI_umowy wfu on u.NUMER_REFERENCYJNY = wfu.numer JOIN WH_ODS.KLIENCI K ON U.KLIENT_ID= K.KLIENT_ID
What is the simplest way to make update with few joins on Oracle?
that method returns error:
UPDATE (SELECT wfu.klient_id_crma as OLD, table2.KLIENT_PROD_ID as NEW FROM nest_crma.WNIOSKI_umowy wfu INNER JOIN (SELECT u.numer_referencyjny,k.KLIENT_PROD_ID from wh_ods.umowy u JOIN WH_ODS.KLIENCI K N U.KLIENT_ID= K.KLIENT_ID) table2 ON wfu.numer = table2.numer_referencyjny ) t SET t.OLD = t.NEW
ORA-01779: cannot modify a column which maps to a non key-preserved
2 Solutions collect form web for “Transform update statement with joins from SQL Server into Oracle”
With something like this it should work
update WNIOSKI_umowy wfu set (klient_id_crma,umowa_id_crma) = ( select K.KLIENT_PROD_ID, u.umowa_id from wh_ods.umowy u JOIN WH_ODS.KLIENCI K ON U.KLIENT_ID= K.KLIENT_ID where u.NUMER_REFERENCYJNY = wfu.numer);
Your update depends on two lookups: you look up
wnioski_umowy.number in the
numer_referencyjny column in table
umowy, and then you further look up
umowy.klient_id in the column
klient_id of table
These lookups require that
klienci be unique (that is, they have no duplicate values). That is an obvious logical requirement that has nothing to do with Oracle, SQL, and computers in general; a lookup must be unique or else what are you using for the update?
There are a few possibilities.
If these columns include duplicates, then the problem is nonsensical.
If the columns do not contain duplicates, but Oracle doesn’t know about that, then updating through join will fail, with the same error as if the columns did have duplicates. The reason is that the Oracle parser judges the correctness of the query BEFORE processing the input data, not after inspecting it. So – if there is no UNIQUE index on each of those two columns, Oracle doesn’t know there are no duplicates (even if you know that).
To make the UPDATE through JOIN work, you will need to add UNIQUE indexes on those two columns first. (Which will also help improve performance!) You can see a very simple illustration of this here: Update with joins
A couple of additional observations: In your existing SQL Server query you are updating two columns. In your attempted Oracle query you are only updating one column. Perhaps you are just trying to “make it work” and then convert the update fully. And, it is not clear why you are separating one join into a subquery – the “update through join” should work if you join the three tables without any use of parentheses.