Update avec jointure : des évolutions ?

Version imprimable

Bonjour,

Je travaille sous Oracle 10.1.0.5.0

J'ai une table "pro" dont la clé est "codsoc, codpro".
J'ai une seconde table "prm" dont la clé est "codsoc, codpro, codlan".

J'ai modifié en masse la table "prm". J'ai modifié ces champs "datmod et utimod" avec la date du jour et mon nom afin d'avoir une trace de qui a modifié ces libellés de produits.

Maintenant, je désire mettre à jour la table "pro" avec le même datmod et utimod pour toutes les lignes que j'ai modifié dans "prm" (en reprenant la liaison sur codsoc, codpro).

Voici la requête que j'ai écrit (qui fonctionne) :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 update pro set (datmod, utimod) = ( select prm.datmod, prm.utimod from prm where prm.codsoc = 100 and prm.codlan = 'ITA' and prm.tradesig4 = 'IVA21' and prm.codpro = pro.codpro ) where (codsoc, codpro) in ( select prm.codsoc, prm.codpro from prm where prm.codsoc = 100 and prm.codlan = 'ITA' and prm.tradesig4 = 'IVA21' );
(codsoc = 100, codlan = 'ITA' et prm.tradesig4 = 'IVA21' permettant de retrouver les lignes que j'ai modifié).

Comme on voit, on retrouve deux fois plus ou moins la même sous-requête. Outre le problème de performances, cette écriture est particulièrement crade.

Existe-t-il une syntaxe plus propre ?

SQL Server permet notamment les jointures sur les update, ce qui rend l'écriture de telles requêtes de mise à jour plus simple.

28/09/2011, 14h30
punkoff

Bonjour,

très bon article ici : http://lnavarro.developpez.com/oracle/updatemerge/
28/09/2011, 14h39
StringBuilder

Merci pour l'astuce du MERGE.

Même si je suis pas fan de sa syntaxe, je vais tenter de me familiariser avec, c'est toujours mieux que la bouse que j'utilise habituellement :)

Ce serait quand même plus pratique si la jointure était supportée :aie:
28/09/2011, 14h43
mnitu

Citation:

Envoyé par StringBuilder

...
Ce serait quand même plus pratique si la jointure était supportée :aie:

En fait elle est supportée depuis Oracle 7.3
28/09/2011, 15h35
StringBuilder

Avec quelle syntaxe ?

Je connais cette syntaxe aussi, mais c'est limite si elle est pas pire que les deux autres réunie...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
begin
  for r in (
    select <id>,
           substr(prm.tradesig2, 1, 30) as new_nompro,
           tradesig3 as new_tradesig1
    from prm, pro
    where prm.codsoc = 3
    and prm.codlan in ('MAG', 'ENG')
    and pro.codsoc = prm.codsoc
    and pro.codpro = prm.codpro 
    and pro.sigfou = 'CADES'
  )
  loop
    update prm
    set nompro = r.new_nompro,
        tradesig1 = r.new_tradesig1
    where <id> = r.<id>;
  end loop;
end;

(une vieille requête que j'ai retrouvé sur un autre forum, que j'avais posté en 2005)

28/09/2011, 15h54
punkoff

Bein dans le document joint (mon 1er poste), il y a 4 types d'updates différent, la syntaxe y est.
28/09/2011, 16h03
StringBuilder
Je suis désolé, mais dans le document, je vois :
- Utilisation d'un update conventionnel
=> Ce que j'ai fais.

- Utilisation de l'instruction Merge
=> Ce vers quoi je vais certainement m'orienter à l'avenir

- Utilisation d'un Update de sous-requête
=> Chose qui me semble assez aléatoire en termes de résultats, pas certain qu'on puisse mettre à jour systématiquement ce qu'on veut de cette façon (même limitation que sur les UPDATE de VIEW)

Je ne vois donc que 3 méthodes d'update.

Vous pouvez d'ailleurs ajouter celle que j'ai rajouté (pourrie, certes, mais qui a le mérite d'exister)

En revanche, aucune des méthodes décrite ne fait de jointure.

Une jointre sur update, c'est ça :
Code:

1 2 3 4 5 update A set A.val = B.val from B where B.ID = A.ID;
(le FROM étant un from de requête classique, avec tout ce qui est permis : jointures, sous-requêtes, etc.)

cette syntaxe là ne convient-elle pas ?
Code:

1 2 3 4 5 update (select sal,Montant from augmentation a join bigemp e on a.empno=e.empno) set sal=sal+Montant
Ce qui devrait donner (je n'ai pas Oracle sous la main pour tester la syntaxe) :
Code:

1 2 3 4 5 6 7 8 9 10 update (select prm.datmod, prm.utimod, pro.datmod as pro_datmod, pro.utimod as pro_utimod from prm inner join pro on prm.codpro = pro.codpro WHERE prm.codsoc = 100 AND prm.codlan = 'ITA' AND prm.tradesig4 = 'IVA21' AND prm.codpro = pro.codpro) set pro_datmod = datmod, pro_utimod = utimod;
Je ne vois pas bien en quoi il va différer de votre dernière proposition au niveau des UPDATE possibe (vu que grosso-modo on va tomber dans les même cas bizarre au vu de la syntaxe) ?

sinon oui vous avez raison, ce n'est pas 4 syntaxes différentes mais 4 méthodes ;)

28/09/2011, 16h53
mnitu
Citation:
Envoyé par StringBuilder

...
Une jointre sur update, c'est ça :

Code:

1 2 3 4 5 update A set A.val = B.val from B where B.ID = A.ID;

(le FROM étant un from de requête classique, avec tout ce qui est permis : jointures, sous-requêtes, etc.)
En gros c'est la même chose avec une différence de syntaxe.
28/09/2011, 17h04
StringBuilder
Citation:
Envoyé par punkoff

cette syntaxe là ne convient-elle pas ?

Code:

1 2 3 4 5 update (select sal,Montant from augmentation a join bigemp e on a.empno=e.empno) set sal=sal+Montant
Cette syntaxe équivaut à un UPDATE sur une VIEW, avec certainement exactement les mêmes contraintes, qui sont loin d'être négligeables.
Il sert à rien de la comparer à la première syntaxe que j'ai posté, puisque je cherche justement à la remplacer par mieux.
A la limite, je préfère le MERGE ou même mon FOR ... LOOP.
D'autant que cette version porte à confusion.

Citation:

Envoyé par punkoff

sinon oui vous avez raison, ce n'est pas 4 syntaxes différentes mais 4 méthodes ;)

Non, dans l'article, moi je ne vois que 3 méthode. Il y a juste une méthode découpée en deux avec un test sur des index (j'ai pas lu en détail), mais la requête est rigoureusement la même, seule l'architecture de la base change.
28/09/2011, 17h17
punkoff
Citation:

Envoyé par StringBuilder

Cette syntaxe équivaut à un UPDATE sur une VIEW, avec certainement exactement les mêmes contraintes, qui sont loin d'être négligeables.
Il sert à rien de la comparer à la première syntaxe que j'ai posté, puisque je cherche justement à la remplacer par mieux.
A la limite, je préfère le MERGE ou même mon FOR ... LOOP.
D'autant que cette version porte à confusion.
.

Je pense qu'elle correspond surtout à la syntaxe que vous avez spécifié en dernier :
Code:

1 2 3 4 5 UPDATE A SET A.val = B.val FROM B WHERE B.ID = A.ID;
Voyez-vous une différence notoire, avec ceci ?
Code:

1 2 3 4 5 UPDATE (select a.val1, b.val2 from A inner join B on a.id = b.id) set val1 = val2;
28/09/2011, 17h30
mnitu

Update

Citation:

Use caution when specifying the FROM clause to provide the criteria for the update operation. The results of an UPDATE statement are undefined if the statement includes a FROM clause that is not specified in such a way that only one value is available for each column occurrence that is updated, that is, if the UPDATE statement is not deterministic. This can cause unexpected results. For example, in the UPDATE statement in the following script, both rows in Table1 meet the qualifications of the FROM clause in the UPDATE statement; but it is undefined which row from Table1 is used to update the row in Table2.

En gros c'est la même chose: dans un cas un SGBD fait n'importe quoi dans un autre il y a une erreur! Mais si vous cherchez à avoir un résultat correct il y a une seule façon de procéder.

Citation:
Envoyé par punkoff
Je penses qu'elle correspond surtout à la syntaxe que vous avez spécifié en dernier :
Code:

1 2 3 4 5 UPDATE A SET A.val = B.val FROM B WHERE B.ID = A.ID;
Voyez-vous une différence notoire, avec ceci ?
Code:

1 2 3 4 5 UPDATE (select a.val1, b.val2 from A inner join B on a.id = b.id) set val1 = val2;
Oui :
1/ Utilisation d'une sous-requête, avec tout ce que cela implique quant aux performances.
2/ Impossibilité de mettre à jour A si plusieurs lignes de B correspondant à une même ligne de A (restrictions sur les update de jointure, comme je le disais).

Exemple :

EVE : Table des livraisons (enfin, dans notre cas, c'est des livraisons)
EVP : Table du détail des livraisons

Comme vous le savez, la TVA italienne est passée de 20% à 21% le 17 septembre dernier.

Suite au changement de paramétrage un peu tardif, je dois mettre à jour en passe toutes les livraisons saisies avec une date de livraison postérieure au 17 septembre.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 update ( select evp.codtva, evp.datmod, evp.utimod from eve inner join evp on evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve where eve.codsoc = 104 and eve.achvte = 'V' and eve.typeve = 'LIV' and eve.codeta = 'V' and eve.dateve >= '20110917' and evp.codtva = '3' ) set codtva = '4', datmod = to_char(sysdate, 'YYYYMMDD'), utimod = 'TVA21'; -- Cette requête passe très bien, la syntaxe est presque séduisante. update ( select eve.datmod, eve.utimod from eve inner join evp on evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve where eve.codsoc = 104 and eve.achvte = 'V' and eve.typeve = 'LIV' and eve.codeta = 'V' and evp.datmod = to_char(sysdate, 'YYYYMMDD') and evp.utimod = 'TVA21' ) set datmod = to_char(sysdate, 'YYYYMMDD'), utimod = 'TVA21'; -- Et c'est le drame : Erreur à la ligne de commande : 28, colonne : 4 Rapport d'erreur : Erreur SQL : ORA-01779: impossible de modifier une colonne correspondant a une table non protegee par cle 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.
Donc non, ce n'est pas équivalent à une jointure : SQL Server sait parfaitement faire ce genre de modifications. On est dans le cas où effectivement, on met à jour EVE sans savoir pour quelle ligne de EVP on le fait, mais ça tombe bien, on veut la même valeur dans EVE du moment qu'une ligne de EVP répond à la jointure.

Ces deux syntaxes n'ont donc absolument RIEN d'équivalent, ni d'un point de vue performances (plan d'exécution), ni d'un point de vue fonctionnalité (l'un utilise une simple jointure, quand l'autre utilise une vue).

Du coup pour la seconde requête, je suis obligé d'utiliser cette requête :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 update eve set datmod = to_char(sysdate, 'YYYYMMDD'), utimod = 'TVA21' where exists ( select null from evp where evp.codsoc = eve.codsoc and evp.achvte = eve.achvte and evp.typeve = eve.typeve and evp.numeve = eve.numeve and evp.datmod = to_char(sysdate, 'YYYYMMDD') and evp.utimod = 'TVA21' ) and codsoc = 104 and achvte = 'V' and typeve = 'LIV' and codeta = 'V' and dateve >= '20110917';
Et ça me donne des boutons. Mais bon, un jour peut-être...

28/09/2011, 17h47
Waldar

StringBuilder, la syntaxe FOR LOOP c'est du PL/SQL, pas un ordre SQL simple, et en terme de temps d'exécution c'est probablement ce qu'il y a de pire.
À éviter tant que faire se peut.

Pour continuer sur le sujet, j'avoue que j'aime bien la syntaxe de SQL-Server, mais elle est propriétaire à MS et hors norme.

MERGE à l'avantage d'être décris dans SQL:2003, supporté chez Oracle depuis Oracle 9i, et chez Microsoft depuis SQL-Server 2008 (ainsi que DB2 et FireBird si j'en crois wikipedia) , et sera de plus en plus adapté.

Avec toutes les facilités d'écriture qu'il contient, il n'y a aucune raison de ne pas l'utiliser (c'est ma méthode préférée vous l'aurez compris).
28/09/2011, 17h53
StringBuilder

C'est effectivement cette syntaxe que je vais utiliser à l'avenir.
Je la trouve lourde et peu claire, mais elle a le mérite d'être normalisée et de répondre à tous les cas.
29/09/2011, 09h07
mnitu

Citation:

Envoyé par StringBuilder

Oui :
1/ Utilisation d'une sous-requête, avec tout ce que cela implique quant aux performances.
2/ Impossibilité de mettre à jour A si plusieurs lignes de B correspondant à une même ligne de A (restrictions sur les update de jointure, comme je le disais).
...

1) C'est faux!
2) Comme je vous disais il y une différence philosophique : un produit vous laisse faire n’importe quoi un autre vous tape sur les doigts.
Et comment faite-vous quand ça ne tombe plus bien ?

Mais pourquoi aller plus loin?

Citation:

Envoyé par StringBuilder
Voici la requête que j'ai écrit (qui fonctionne) :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
UPDATE pro
SET (datmod, utimod) = 
(
   SELECT prm.datmod, prm.utimod
   FROM prm
   WHERE prm.codsoc = 100
   AND prm.codlan = 'ITA'
   AND prm.tradesig4 = 'IVA21'
   AND prm.codpro = pro.codpro
)
WHERE (codsoc, codpro) IN 
(
   SELECT prm.codsoc, prm.codpro 
   FROM prm
   WHERE prm.codsoc = 100
   AND prm.codlan = 'ITA'
   AND prm.tradesig4 = 'IVA21'
);

il a déclaré que ça a marché, il lui suffit juste une optimisation de la requete; pour cela pourquoi pas ça.

Code:

1
2
3
4
5
6
7
8
9
10
11
UPDATE pro
SET (datmod, utimod) = 
(
   SELECT prm.datmod, prm.utimod
   FROM prm
   WHERE prm.codsoc = 100
   AND prm.codlan = 'ITA'
   AND prm.tradesig4 = 'IVA21'
   AND prm.codpro = pro.codpro
   and pro.codsoc=prm.codsoc --ligne ajoutée
)

C'est le

Code:

IN

qui prend plus de temps dans les requete

29/09/2011, 16h56
mnitu

Parce que dans ce cas les enregistrements qui n'existent pas dans prm vont être mises à jour avec null.