Amélioration d'un case

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:

---

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.

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:

---

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...)

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:

---

1 2 3 4

EXCEPTION when others then if sqlcode = -1407 then null;

---

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

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

Code:

---

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é

Citation:

---

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:

---

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

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.

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

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.

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.

Citation:

---

Envoyé par Scriuiw

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:

---

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.

---

Ok ça marche mais ça me donne ceci:

Code:

---

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.

Exact McM, comme quoi il est toujours bon de tester :ccool:

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

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

Citation:

---

Code:

---

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:

---

1 2 3 4 5 6 7

UPDATE tableUPD U [..] WHERE EXISTS (SELECT 1 FROM tables WHERE jointure)

---

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.

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.

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

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.

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.