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?

  • Connecting to a MS SQL server from a different machine running PHP
  • How to get “1” if value last in “OVER(order by something)” group, and “0” if not?
  • SQLBindParameter to prepare for SQLPutData using C++ and SQL Native Client
  • Why a simple T-SQL UDF function makes the code execution 3 times slower
  • Microsoft SQL Server 2014: Incorrect Generated Script Ordering when Export Self-Referenced Table to SQL
  • ODBC Call Failed with stored procedure - Pass through query
  • 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

  • Delete SQL Server 2005 records without logging
  • SQL Server 2000 - Query a Table’s Foreign Key relationships
  • Table Value Function, Select Multiple Rows
  • How to find out Database calls triggered by a specific IIS request
  • Sql compare TimeStamp with normal date
  • SQL: how to get the left 3 numbers from an int
  • 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 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.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.