Update with complex joins

Question

I'm trying to update a table with a kind of complex source dataset. There is a table parcel which contains stock information about products. it also contains a field product description. This field sometimes still has some old descriptions and needs to be updated by the base data table.

but the joined table needs also to be filtered by a language which is done by another subselect or join.

SELECT products.DESCSH as old_desc
        ,parcel.DESCSH  as new_desc
    FROM parcel
        ,products
   WHERE parcel.PRODU_INTERNUM = products.INTERNUM
     AND parcel.NUM LIKE '2%REGEN'
     and (SELECT COMPA.LANG FROM COMPA WHERE COMPA.NUM = parcel.COMPA_NUM)  = products.LANG
     and trim(upper(products.DESCSH)) <> trim(upper(parcel.DESCSH))

this is the base select. (the task is basically to update new_desc with old_desc)

Now since i had no idea how to do this i saw 3 methods here. The MERGE approach

 merge into gc_prcel target
  using (
         Select p.descsh
               ,p.internum
           from p 
               ,par
          where sysdate between p.vldty_beg and p.vldty_end
            and p.internum = par.produ_internum
            and p.lang = (SELECT COMPA.LANG FROM COMPA WHERE COMPA.NUM = par.COMPA_NUM)
        ) source
    on (target.produ_internum = source.internum)
   when matched then update
    set target.descsh = source.descsh 
  where target.num like '2%REGENSDO'
    and trim(upper(target.descsh)) <> trim(upper(target.descsh));

This one either updates almost the whole table when it only should update 400 records and i'm clueless why or it gets stuck in an endless loop (even more clueless). I know it looks weird i tried out lots of different places for those where clauses but none of them worked.

The method with the EXIST clause (i could use this one but i find the other approaches more elegant) and the one where you directly update the field from the subselect like that:

update
    (SELECT products.DESCSH as old_desc
            ,parcel.DESCSH  as new_desc
        FROM parcel
            ,products
       WHERE parcel.PRODU_INTERNUM = products.INTERNUM
         AND parcel.NUM LIKE '2%REGENSDO'
         and (SELECT COMPA.LANG FROM COMPA WHERE COMPA.NUM = parcel.COMPA_NUM)  = products.LANG
         and trim(upper(products.DESCSH)) <> trim(upper(parcel.DESCSH))) descriptions
  set descriptions.new_desc = descriptions.old_desc;

this one throws (ORA-01779:cannot modify a column which maps to a non key-preserved table)

Is the task i'm trying to do even possible with a merge statement or do i have to use the update with exists and why does the my first approach fail with ORA-01779)

Answer 1

You are doing the smart thing - both in MERGE and in UPDATE - which is to update a row only when the value does change. But in MERGE you wrote this - can you spot the mistake? (Which is perhaps the reason, or one of the reasons, for the long execution time.)

and trim(upper(target.descsh)) <> trim(upper(target.descsh));

OK, this shouldn't be a guessing game; one of the arguments should be source.deschs, you have target on both sides.

Edit: Or actually wait, this should cause the statement to not update anything since that condition is never TRUE. Is that EXACTLY the statement you used? End Edit

For UPDATE and the error you got, I wrote this short article in the "Documentation" domain of Stack Overflow. It explains the error you got, and how to fix it: Update with joins

Answer 2

If the gc_prcel and par (I assume this is really the parcel table that you mention in your update statements?) are different tables, then I'd do a merge, something like:

merge into gc_prcel target
  using (
         Select p.descsh
               ,p.internum
           from p
                inner join par on p.internum = par.produ_internum
                                  and trim(upper(p.descsh)) <> trim(upper(par.descsh)
          where sysdate between p.vldty_beg and p.vldty_end
            and p.lang = (SELECT COMPA.LANG FROM COMPA WHERE COMPA.NUM = par.COMPA_NUM)
            and par.num like '2%REGENSDO');
        ) source
    on (target.produ_internum = source.internum)
   when matched then update
    set target.descsh = source.descsh;

If, however, it's the parcel table you're trying to update, I'd go with something like:

merge into parcel tgt
  using (select p.internum,
                p.descsh,
                compa.num
         from   products p
                inner join compa on p.lang = compa.lang
         where  sysdate between p.vldty_beg and p.vldty_end) src
    on (tgt.produ_internum = src.internum
        and tgt.num like '2%REGENSDO'
        and tgt.compa_num = src.compa_num)
when matched then
  update set tgt.descsh = src.descsh
  where      trim(upper(tgt.descsh)) != trim(upper(src.descsh));