UPDATE : vue de jointure in-line

Update de vue de jointure "in-line" et index unique

 

SQL> create table SRC (a number, b number);

Table created.

SQL> create table CIB (a number, b number);

Table created.

SQL> insert into SRC values (1,11);

1 row created.

SQL> insert into SRC values (2,12);

1 row created.

SQL> insert into CIB values (1,0);

1 row created.

SQL> insert into CIB values (2,0);

1 row created.

SQL> select * from SRC;

         A          B
---------- ----------
         1         11
         2         12

SQL> select * from CIB;

         A          B
---------- ----------
         1          0
         2          0

SQL> update (
  2     select SRC.b srcb, CIB.b cibb
  3     from SRC, CIB
  4     where SRC.a=CIB.a
  5  )
  6  set cibb = srcb
  7  ;
set cibb = srcb
    *
ERROR at line 6:

ORA-01779: cannot modify a column which maps to a non key-preserved table

Un index unique doit être créé sur la clé de la table source

SQL> create unique index IU_SRC on SRC (a);

Index created.

SQL> update (
  2     select SRC.b srcb, CIB.b cibb
  3     from SRC, CIB
  4     where SRC.a=CIB.a
  5  )
  6  set cibb = srcb
  7  ;

2 rows updated.

SQL> select * from SRC;

         A          B
---------- ----------
         1         11
         2         12

SQL> select * from CIB;

         A          B
---------- ----------
         1         11
         2         12