Discussion :

[Haywire]

Bonjour,

J'ai un update qui utilise un case pour affecter une valeur selon une condition.

Le case fait un select et je veux que la valeur updaté soit le résultat du select, ou 150000 si le résultat est plus grand que 150000.

En d'autres terme je veux faire un update dont la valeur ne doit pas dépasser 150000.

J'ai donc codé ceci (j'ai éliminé tout ce qui n'est pas nécessaire à la compréhension du problème):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
update ma_table set 
      amount = 
        case
          when (select ...) > 150000 then 150000
          else (le même select)
        end;

Je me demandais s'il est possible de formuler ça de manière à ne pas répéter 2 fois le même select.
Bien que j'imagine qu'oracle est assez intelligent pour ne pas exécuter le select deux fois et mettre le résultat en cache, mais comme je n'en suis pas sûr...

Merci d'avance.

[Scriuiw]

Ton besoin est donc si ton SELECT ramène quelque chose d'inférieure à 150000 affecter cette valeur sinon une valeur Maximum

Tu peux essayer ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
UPDATE Ma_table
SET Ma_zone = 
    (SELECT LEAST(Valeur,150000)
    FROM
       (SELECT  Valeur
       FROM      Autre_table
       WHERE   Une_clause)
   );

PS : A voir aussi ce que tu veux faire si la requête ramène NULL (utilisation NVL, DECODE, clause WHERE pour l'UPDATE...)

[Haywire]

Merci, je n'avais pas pensé au LEAST, c'est bien plus élégant comme ça.

Pour le null, pour le moment je le gère avec une EXCEPTION.
Si le select renvoie null je ne dois tout simplement pas faire l'update.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
EXCEPTION   
  when others then
    if sqlcode = -1407 then
      null;

Mais peut-être qu'il y a plus efficace ?

[McM]

un sous select qui ne renvoie aucune ligne ne sort pas en no_data_found, mais est égal à NULL

[Scriuiw]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SQL> SELECT LEAST(Valeur,150000)
  2      FROM
  3         (SELECT  3200000 AS Valeur
  4         FROM      dual
  5         WHERE   1=0);
 
aucune ligne sélectionnée

Pas dans ce cas-ci, c'est bien un NO_DATA_FOUND qui est retourné

Mais peut-être qu'il y a plus efficace ?

Tout dépend ce que tu veux faire !


EDIT : Par contre oui McM, vu que la requête est un UPDATE, tu n'auras pas de NO_DATA_FOUND mais aucune ligne de mise à jour

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
SQL> UPDATE t
  2  SET a = (SELECT LEAST(Valeur,150000)
  3      FROM
  4         (SELECT  3200000 AS Valeur
  5         FROM      dual
  6         WHERE   1=0));
 
0 ligne(s) mise(s) à jour.

Du coup, ce n'est peut-être pas le résultat que tu attends

[Haywire]

En fait comme le résultat du select est utilisé dans un update sur une colonne qui ne peut pas être null, je reçois l'erreur -1407: cannot insert null value in.
Donc je catch cette exception comme montré dans le code plus haut.

Ca me convient mais peut-être qu'il y a plus performant. Je sais qu'il y a un overhead quand oracle doit gérer une exception.

[Scriuiw]

La seule solution plus rapide que j'entrevois est d'éviter l'erreur via la clause WHERE de ton UPDATE

[Haywire]

Si j'ajoute une clause where à mon select, je vais me retrouver avec un NO_DATA_FOUND, ce qui revient au même.

Et ajouter un where sur l'update qui ne ferait pas l'update si le select renvoit null, je ne vois pas comment.

Mais bon c'est pas très important, je n'ai pas réellement de problème de performance à cet endroit.

[Scriuiw]

Pas de NO_DATA_FOUND avec un UPDATE mais un SQL%ROWCOUNT=0
Donc pas d'erreur ni exception retournée.

Si le prédicat dans ta clause WHERE ne retourne pas de ligne, il n'y aura pas de mise à jour à NULL car il n'y a aucune ligne correspondant à la sélection.

[McM]

EDIT : Par contre oui McM, vu que la requête est un UPDATE, tu n'auras pas de NO_DATA_FOUND mais aucune ligne de mise à jour

Pas du tout ! Un update sans clause where update toutes les lignes !

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE TABLE T1 (a number);
 
INSERT INTO t1 
SELECT ROWNUM FROM all_tables WHERE ROWNUM < 5
4 Rowsinserted
 
COMMIT;
 
UPDATE t1
  SET a = (SELECT LEAST(Valeur,150000)
      FROM
         (SELECT  3200000 AS Valeur
        FROM      dual
         WHERE   1=0));
 
4 rows updated 
 
         select * from t1
=> 4 lignes vides.

[Haywire]

Ok ça marche mais ça me donne ceci:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
update ma_table set 
      amount = (select least((select lim_bd from table1 where qqch), 150000) from table1 where même chose);

Donc je select 2 fois dans la même table et selon la même condition.
Je ne sais pas si ce sera vraiment plus performant que la gestion d'exception ?

EDIT: Rectification, ça ne marche pas. Le select seul ne renvoit pas de résultat, effectivement, mais si je le met dans l'update j'ai toujours l'erreur cannot update to null donc oracle converti le manque de résultat en null.

[Scriuiw]

Exact McM, comme quoi il est toujours bon de tester

Haywire, si ta requête est au sein d'une boucle, l'erreur sera interceptée ligne à ligne, si par contre c'est pour un ensemble de données, une seule erreur (MàJ à NULL d'une zone NOT NULLABLE) et toute ta transaction sera annulée

[Haywire]

J'ai édité mon message, finalement ça ne fonctionne pas.
Tant pis je laisse l'exception.

[Scriuiw]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
UPDATE ma_table SET 
      amount = (SELECT least((SELECT lim_bd FROM table1 WHERE qqch), 150000) FROM table1 WHERE même chose);

Ta clause WHERE doit d'avantage t'assurer qu'il existe bien un enregistrement pour ne pas tomber dans le cas du NULL

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
UPDATE tableUPD U
[..]
WHERE EXISTS
  (SELECT 1 
  FROM tables
  WHERE jointure)

[Haywire]

Justement il n'y pas toujours un enregistrement.

En fait si lim_bd (la valeur que je veux comparer à 150000 et updaté) n'existe pas pour ces critères, c'est le record entier qui n'existe pas.

Je pense donc qu'il n'y pas d'autre solution que l'exception.

[Scriuiw]

A moins de nécessité d'un suivi des erreurs ou pour raison de logs je pencherais plus pour le test d'existence de(s) l'enregistrement(s) plutôt que d'intercepter l'erreur d'inexistence.

Après selon tes dires j'en conclus que l'UPDATE met à jour une seule ligne et non un ensemble, donc l'interception est jouable.

[Haywire]

Oui en fait le but est de mettre à jour 1 ligne, si cette ligne existe.
Si elle n'existe pas, on ne fait rien.

[Waldar]

On peut aussi arrêter les devinettes, modéliser les deux tables avec quelques données, l'association qui les relie et ce que vous voulez en faire !

Il n'y a pas besoin d'attraper des exceptions en SQL pour ne pas faire de mise à jour, comme dit un peu plus haut il suffit d'être assez restrictif dans la condition de jointure.

[Haywire]

J'ai réussi en remettant le select dans la clause where de l'update (where select is not null).

Il n'y a pas moyen de faire une jointure entre les deux tables car elles ne sont pas dans la même db et n'ont aucun champ commun.

Le critère qui me permet de savoir quel row je dois essayer d'updater m'est fourni par le résultat de traitements précédents.

Ce serait un peu trop long a expliquer mais c'est dans le contexte d'une migration des données d'un système vers un autre système.

L'important est que ça fonctionne maintenant.

Merci à vous.