Oracle Database
Mise à jour avec des jointures

Introduction

Exemples: ce qui fonctionne et ce qui ne fonctionne pas

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.

Imaginez ce qui arriverait si nous avions la valeur 1 dans la colonne src.id plus d'une fois, avec des valeurs différentes pour src.val . Evidemment, la mise à jour n'aurait aucun sens (dans N'IMPORTE QUELLE base de données, c'est une question logique). Maintenant, nous savons qu'il n'y a pas de doublons dans src.id , mais le moteur Oracle ne le sait pas - alors il se plaint. Peut-être est-ce la raison pour laquelle tant de praticiens pensent qu'Oracle "n'a pas mis à jour les jointures"?

Oracle s'attend à ce que src.id soit unique et qu’Oracle le sache au préalable. Facilement réparé! Notez que la même chose fonctionne avec les clés composites (sur plusieurs colonnes), si la correspondance pour la mise à jour doit utiliser plusieurs colonnes. En pratique, src.id peut être PK et tgt.id peut être FK pointant vers cette PK, mais cela n'est pas pertinent pour les mises à jour avec jointure; ce qui est pertinent est la contrainte unique.

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

Le même résultat pourrait être obtenu avec une instruction MERGE (qui mérite son propre article Documentation), et je préfère personnellement MERGE dans ces cas-là, mais la raison n'est pas que "Oracle ne fait pas de mises à jour avec des jointures". Comme le montre cet exemple, Oracle fait faire des mises à jour avec des jointures.