Transform update statement with joins from SQL Server into Oracle

This update statement works perfectly on SQL Server, but on Oracle doesn’t.

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

What is the simplest way to make update with few joins on Oracle?

  • Using Temp table in A VIEW
  • Group rows by contiguous date ranges for groups of values
  • Execute stored procedure by passing the Script File(.sql) as Parameter
  • SQL Escape '#' Symbol
  • Microsoft SQL Server 2005/2008: XML vs text/varchar data type
  • Import txt file from web form to sqlserver database using vbscript
  • that method returns error:

    (SELECT wfu.klient_id_crma as OLD, table2.KLIENT_PROD_ID as NEW
     FROM nest_crma.WNIOSKI_umowy wfu
    (SELECT u.numer_referencyjny,k.KLIENT_PROD_ID
    from wh_ods.umowy u
    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

  • What does a timestamp in T-Sql mean in C#?
  • SQL Server 2008 timestamp data type
  • Python multiprocessing and database access with pyodbc “is not safe”?
  • Should I use a push or pull subscription if using RMO?
  • SQL Server Performance With Large Query
  • PostgreSQL equivalent for TOP n WITH TIES: LIMIT “with ties”?
  • 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
        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.