Oracle Database
Uppdatera med Joins

Introduktion

Exempel: vad som fungerar och vad som inte fungerar

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.

Föreställ dig vad som skulle hända om vi hade värdet 1 i kolumnen src.id mer än en gång, med olika värden för src.val . Uppenbarligen skulle uppdateringen inte vara meningsfull (i någon databas - det är en logisk fråga). Nu vet vi att det inte finns några dubbletter i src.id , men Oracle-motorn vet inte det - så det klagar. Kanske är det därför som så många utövare tror att Oracle "inte har UPPDATERING med sammanfogningar"?

Vad Oracle förväntar sig är att src.id borde vara unikt och att det, Oracle, skulle veta det i förväg. Lätt fixad! Observera att samma fungerar med sammansatta nycklar (i mer än en kolumn), om matchningen för uppdateringen måste använda mer än en kolumn. I praktiken kan src.id vara PK och tgt.id kan vara FK som pekar på denna PK, men det är inte relevant för uppdateringar med join; vad som är relevant är den unika begränsningen.

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

Samma resultat kan uppnås med ett MERGE-uttalande (som förtjänar en egen dokumentationsartikel), och jag föredrar personligen MERGE i dessa fall, men orsaken är inte att "Oracle inte gör uppdateringar med anslutningar." Som Detta exempel visar, Oracle gör sidan går.