Oracle Database
Update met Joins

Invoering

Voorbeelden: wat werkt en wat niet

create table tgt ( id, val ) as 
  select 1, 'a' from dual union all
  select 2, 'b' from dual
;

Table TGT created.

create table src ( id, val ) as 
  select 1, 'x' from dual union all
  select 2, 'y' from dual
;

Table SRC created.

update
  ( select t.val as t_val, s.val as s_val
    from   tgt t inner join src s on t.id = s.id
  )
set t_val = s_val
;

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Stel je voor wat er zou gebeuren als we de waarde 1 in de kolom src.id meer dan eens hadden, met verschillende waarden voor src.val . Het is duidelijk dat de update geen zin heeft (in ELKE database - dat is een logisch probleem). Nu weten we dat er geen duplicaten zijn in src.id , maar de Oracle-engine weet dat niet - dus het is klagen. Misschien is dit de reden waarom zoveel beoefenaars geloven dat Oracle "geen UPDATE met joins heeft"?

Wat Oracle verwacht, is dat src.id uniek moet zijn en dat Oracle dat van tevoren zou weten. Eenvoudig opgelost! Merk op dat hetzelfde werkt met samengestelde sleutels (op meer dan één kolom), als de aanpassing voor de update meer dan één kolom moet gebruiken. In de praktijk kan src.id PK zijn en kan tgt.id FK zijn die naar deze PK tgt.id , maar dat is niet relevant voor updates met join; wat relevant is, is de unieke beperking.

alter table src add constraint src_uc unique (id);

Table SRC altered.

update
  ( select t.val as t_val, s.val as s_val
    from   tgt t inner join src s on t.id = s.id
  )
set t_val = s_val
;

2 rows updated.

select * from tgt;

ID  VAL
--  ---
 1  x
 2  y

Hetzelfde resultaat kan worden bereikt met een MERGE-instructie (die zijn eigen documentatie-artikel verdient), en ik geef persoonlijk de voorkeur aan MERGE in deze gevallen, maar de reden is niet dat "Oracle geen updates met joins uitvoert". Zoals dit voorbeeld laat zien, doet Oracle wel updates met joins.