UPDATE : critère supplémentaire dans la table source

On souhaite mettre à jour une table CIB à partir d'une table SRC sur un critère de jointure conditionné par un prédicat supplémentaire.

La difficulté est due au fait que la table à mettre à jour contient des lignes qu'il ne faudra pas mettre à jour et que ce critère est indiqué dans la table source (src.c='NON')

Préparation :

SQL> CREATE TABLE src (a NUMBER, b NUMBER, c CHAR(3));

Table created.

SQL> CREATE TABLE cib (a NUMBER, b NUMBER);

Table created.

SQL> INSERT INTO src VALUES (1,11,'OUI');

1 row created.

SQL> INSERT INTO src VALUES (1,12,'NON');

1 row created.

SQL> INSERT INTO src VALUES (2,22,'NON');

1 row created.

SQL> INSERT INTO cib VALUES (1,0);

1 row created.

SQL> INSERT INTO cib VALUES (2,0);

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM src;

         A          B C
---------- ---------- ---
         1         11 OUI
         1         12 NON
         2         22 NON

SQL> SELECT * FROM cib;

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

Exécution :

Rappel : on souhaite mettre à jour la table cib à partir des valeurs de la table src uniquement lorsque la cle de cib est présente dans src et que le champ c de src vaut 'OUI'

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6  )
  7  ;
        SELECT src.b
        *
ERROR at line 3:
 
ORA-01427: single-row subquery returns more than one row
La table source renvoie plus d'une valeur pour une valeur de clé donnée
 

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6  )
  7  WHERE src.c='OUI'
  8  ;
WHERE src.c='OUI'
      *
ERROR at line 7:
ORA-00904: "SRC"."C": invalid identifier

Le prédicat est mal placé

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6     AND src.c='OUI')
  7  ;

2 rows updated.

SQL> SELECT * FROM cib;

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

Les valeurs des clés sans correspondance sont mises à jour par null

SQL> ROLLBACK;

Rollback complete.

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6     AND src.c='OUI')
  7  WHERE EXISTS (
  8     SELECT 1
  9     FROM src
 10     WHERE src.a=cib.a)
 11  )
 12  ;

2 rows updated.

SQL> SELECT * FROM cib;

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

La condition where exists est inefficace, des valeurs des clés sans correspondance sont mises à jour par null

SQL> ROLLBACK;

Rollback complete.

SQL> UPDATE cib
  2  SET cib.b = (
  3     SELECT src.b
  4     FROM src
  5     WHERE src.a=cib.a
  6     AND src.c='OUI')
  7  WHERE EXISTS (
  8     SELECT 1
  9     FROM src
 10     WHERE src.a=cib.a
 11     AND src.c='OUI'
 12  )
 13  ;

1 row updated.

SQL> SELECT * FROM cib;

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

 
Écriture correcte, on a le résultat souhaité

SQL> ROLLBACK;

Rollback complete.

Écriture équivalente sous forme de MERGE

SQL> MERGE INTO cib t_c
  2  USING (
  3     SELECT *
  4     FROM src
  5     WHERE c='OUI'
  6  ) t_s
  7  ON (t_c.a=t_s.a)
  8  WHEN MATCHED THEN
  9  UPDATE SET t_c.b=t_s.b
 10  ;

1 row merged.

SQL> SELECT * FROM cib;

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

SQL> ROLLBACK;

Rollback complete.