CODEWITHSUNDEEP
sql-serveroracle-databasesql-update
Peter_K
Peter_K

Reputation: 103

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 table

Upvotes: 1

Views: 370

Answers (2)

user5683823
user5683823

Reputation:

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 klienci.

These lookups require that numer_referencyjny in umowy and klient_id in 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.

Upvotes: 1

Mynsk
Mynsk

Reputation: 209

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);

Upvotes: 1

Related Questions