Oracle Database
Обновление с помощью объединений

Вступление

Примеры: что работает, а что нет

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.

Представьте, что произойдет, если бы мы имели значение 1 в столбце src.id более одного раза, с разными значениями для src.val . Очевидно, что обновление не имеет смысла (в ЛЮБОЙ базе данных - это логическая проблема). Теперь мы знаем, что дубликатов в src.id , но механизм Oracle не знает этого, поэтому он жалуется. Возможно, именно поэтому так много практикующих полагают, что у Oracle «нет UPDATE с объединениями»?

Oracle ожидает, что src.id должен быть уникальным и что он, Oracle, будет знать это заранее. Легко фиксируется! Обратите внимание, что то же самое работает с составными ключами (более одного столбца), если для соответствия для обновления требуется использовать более одного столбца. На практике src.id может быть PK, а tgt.id может быть FK, указывающим на этот PK, но это не относится к обновлениям с соединением; значение имеет то ограничение уникальности.

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

Тот же результат может быть достигнут с помощью оператора MERGE (который заслуживает своей собственной статьи Документации), и я лично предпочитаю MERGE в этих случаях, но причина не в том, что «Oracle не делает обновлений с объединениями». Как показывает этот пример, Oracle делает обновления с объединениями.