Discussion :

[sas68sas]

Bonjour,

J’ai un problème lors de la mise à jour d’un champ en provenance d’une autre table

Les tables

TS_Articles
Select No_Article_TAN,Stock_Actuel_TA FROM TS_Articles



TS_Transactions
Select [No_Trans_TT], [Date_TT], [No_Article_TT],[Quantite_TT] From TS_Transactions


Je veux mettre à jour TS_Articles.Stock_Actuel_TA avec toutes les valeurs
des enregistrements de Transactions.Quantite_TT correspondants

Résultat escompté dans TS_Articles (4000=2000+1000+400+600)


Avec

Code :

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

1
2
3
4
UPDATE TS_Articles
    SET TS_Articles.Stock_Actuel_TA=TS_Articles.Stock_Actuel_TA+TS_Transactions.Quantite_TT
     FROM  TS_Transactions
     WHERE TS_Transactions.No_Article_TT=TS_Articles.No_Article_TAN

OU Avec

Code :

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

1
2
3
4
UPDATE TS_Articles
    SET TS_Articles.Stock_Actuel_TA=TS_Articles.Stock_Actuel_TA+TS_Transactions.Quantite_TT
     FROM TS_Articles INNER JOIN TS_Transactions 
     ON TS_Articles.No_Article_TAN = TS_Transactions.No_Article_TT

Le résultat obtenu est :


Seul le premier enregistrement est traité (No_trans_TT=214)
pas les autres !!!

Avec

Code :

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

1
2
3
4
UPDATE TS_Articles
    SET TS_Articles.Stock_Actuel_TA=TS_Articles.Stock_Actuel_TA+TS_Transactions.Quantite_TT
     FROM TS_Articles INNER JOIN TS_Transactions 
     ON (TS_Articles.No_Article_TAN = TS_Transactions.No_Article_TT) AND TS_Transactions.No_Trans_TT >=838

Le résultat obtenu est 400 et pas 1000 (400+600)



un seul enregistrement touché ????

Si quelqu'un a une solution, merci d'avance !
Manuel

[Michel.Priori]

Bonsoir

Lire la doc est toujours utile.
En l'occurrence c'est ici : https://docs.microsoft.com/fr-fr/sql...l-server-ver15

Si l'objet mis à jour est le même que l'objet de la clause FROM et s'il n'existe qu'une seule référence à cet objet de la clause FROM, un alias d'objet pourra être spécifié ou non.
Si l'objet mis à jour apparaît plusieurs fois dans la clause FROM, l'une des références, mais une seule, à cet objet ne doit pas spécifier un alias de la table.
Toutes les autres références à l'objet dans la clause FROM doivent inclure un alias d'objet.

[sas68sas]

Bonjour,

Merci pour les infos, mais malgré tous mes essais avec Alias
impossible de mettre à jour [Stock_Actuel_TA] avec totalité des transactions (total : 4000)
Seul le premier enregistrement est traité (total : 2000)


Voici les essais

Code :

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

1
2
3
4
5
UPDATE TS_Articles 
     SET TS_Articles.Stock_Actuel_TA=ART.Stock_Actuel_TA+TT.Quantite_TT
     from TS_Articles AS ART
	 INNER JOIN TS_Transactions as TT
	 ON TT.No_Article_TT=ART.No_Article_TAN

Code :

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

1
2
3
4
5
UPDATE X 
     SET X.Stock_Actuel_TA=X.Stock_Actuel_TA+TT.Quantite_TT
     from TS_Articles as X ,TS_Transactions AS TT
	 INNER JOIN TS_Articles AS ART
	 ON TT.No_Article_TT=ART.No_Article_TAN

Code :

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

1
2
3
4
5
6
7
8
9
UPDATE XX 
     SET XX.Stock_Actuel_TA =XX.Stock_Actuel_TA+XX.Quantite_TT
	 FROM
	 (
		 Select art.No_Article_TAN,TS_Transactions.No_Article_TT,art.Stock_Actuel_TA,TS_Transactions.Quantite_TT
		 FROM TS_Transactions 
                 INNER JOIN TS_Articles AS ART
		 ON TS_Transactions.No_Article_TT=ART.No_Article_TAN
      ) as XX

La solution suivante fonctionne, mais avec une requête qui regroupe
les transactions (SELECT No_Article_TT,SUM(Quantite_TT) as S FROM TS_Transactions GROUP BY No_Article_TT)

Code :

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

1
2
3
4
5
6
UPDATE TS_Articles 
     SET TS_Articles.Stock_Actuel_TA =TS_Articles.Stock_Actuel_TA+ x.s
	 FROM TS_Articles 
         INNER JOIN 
         (SELECT No_Article_TT,SUM(Quantite_TT) as S FROM TS_Transactions GROUP BY No_Article_TT)  as x
	 ON x.No_Article_TT=TS_Articles.No_Article_TAN

Comment faire SANS regroupement si " INNER JOIN " renvoie plus d'un enregistrement !!!!

Merci d'avance
Manuel

[SQLpro]

pour vous aider, merci de respecter la charte de postage en postant le DDL de vos tables ainsi qu'un jeu d'essais sous forme de scripts SQL.

A lire : https://www.developpez.net/forums/d9...vement-poster/

A +

[Michel.Priori]

Bonsoir,

Désolé pour ma réponse trop rapide, je n'ai pas pris le temps de décortiquer la demande, j'ai cru en première lecture qu'il était simplement question de syntaxe.

En reprenant votre demande, je me suis arreté sur :

Résultat escompté dans TS_Articles (4000=2000+1000+400+600)

heu... non. Une simple jointure ne peux pas accéder à votre espoir.
Le code :

Code :

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

SET TS_Articles.Stock_Actuel_TA =TS_Articles.Stock_Actuel_TA+ x.s

indique l’opération algébrique à faire.
Où est la notion de somme ?
Et puis pourquoi, par défaut d'opérateur, cela devrait être une somme ?

Ce qui est fait est bien plus simple : on injecte la première ou dernière valeur trouvée ; donc au choix 2000, 1000, 400 ou 600 car on ne maitrise pas l'ordre d'apparition des valeurs qui respectent la clause WHERE.

Donc la solution à base de group by me parait tout à fait adaptée

Code :

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

1
2
3
4
5
6
7
8
9
10
With Sum_Tran as
    (SELECT No_Article_TT
        ,SUM(Quantite_TT) as Tot
     FROM TS_Transactions 
     GROUP BY No_Article_TT
    )
UPDATE TS_Articles 
     SET TS_Articles.Stock_Actuel_TA = TS_Articles.Stock_Actuel_TA + Sum_Tran.Tot
     FROM TS_Articles 
        inner join Sum_Tran on Sum_Tran.No_Article_TT=TS_Articles.No_Article_TAN;

[sas68sas]

Encore une nouvelle fois merci pour les infos et le temps passé..

Mais ce qui m’interpelle avec la commande UPDATE
c'est justement "l'arrêt" de la résolution de la clause INNER JOIN
OU WHERE dès le premier enregistrement trouvé dans la Table Transactions !!!!
Pourquoi ne traite-t-il pas les autres enregistrements ?????


La table TS_Articles contient 1 enregistrement
No_Article | Stock_Actuel
2_______ | 0

La table TS_Transactions contient 4 enregistrements
No_Article | Quantité
2_______ | 2000
2_______ | 1000
2_______ | 400
2_______ | 600

Je veux obtenir dans la table TS_Articles.Stock_Actuel =4000 (2000+1000+400+600)


SOUS Access ça fonctionne avec une simple requête mise à jour !

Code :

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

1
2
3
4
UPDATE TS_Articles 
INNER JOIN TS_Transactions 
ON TS_Articles.No_Article = TS_Transactions.No_Article
SET TS_Articles.Stock_Actuel = TS_Articles.Stock_Actuel + TS_Transactions.Quantite ;

La requête cumulera dans TS_Articles.Stock_Actuel la totalité de toutes les TS_Transactions.[Quantite]; soit 4000 (2000+1000+400+600)


Sous SQL Server
Avec même logique:

Code :

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

1
2
3
4
UPDATE TS_Articles
SET TS_Articles.Stock_Actuel = TS_Articles.Stock_Actuel + TS_Transactions.Quantite
FROM TS_Articles INNER JOIN TS_Transactions 
ON TS_Articles.No_Article = TS_Transactions.No_Article

OU

Code :

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

1
2
3
4
UPDATE TS_Articles 
SET TS_Articles.Stock_Actuel = TS_Articles.Stock_Actuel+ TS_Transactions.Quantite_TT
FROM TS_Articles , TS_Transactions 
WHERE TS_Articles.No_Article_TAN = TS_Transactions.No_Article_TT

Là dans TS_Articles.Stock_Actuel pas de total (4000)
mais l’une des 4 valeurs possibles de la table TS_Transactions (soit :2000 ou 1000 ou 400 ou 600)
mais pas le cumul ?

Existe-t-il une requête « UPDATE » pour faire cette mise à jour sans devoir passer par un cumul préalable de TS_Transactions.Quantite ?

Merci
Manuel

[aieeeuuuuu]

bonjour,

Non, une instruction UPDATE ne peut pas mettre à jour plusieurs fois la même ligne.

Il faut donc bien passer par une agrégation au préalable. mais cela ne sera pas moins efficace, au contraire... Qu'est-ce qui vous pose problème dans cette solution ?

[aieeeuuuuu]

vous n'avez pas répondu à ma question

Qu'est-ce qui vous pose problème dans cette solution ?

Je parlais de la solution avec regroupement ?

[Michel.Priori]

Bonjour,

Là dans TS_Articles.Stock_Actuel pas de total (4000)
mais l’une des 4 valeurs possibles de la table TS_Transactions (soit :2000 ou 1000 ou 400 ou 600)
mais pas le cumul ?

Existe-t-il une requête « UPDATE » pour faire cette mise à jour sans devoir passer par un cumul préalable de TS_Transactions.Quantite ?

Le codage est l'art d'expliciter ce qu'on veux faire faire à la machine.
Si on veut injecter le résultat d'une opération quelconque, ne fusse qu'un cumul, il faut l'indiquer.

Ceci dit vous pouvez joindre un fichier access avec les tables nécessaires, des données et la requête qui fait la somme mâgique ?

[sas68sas]

Bonjour,

Voici une base Access avec la requête "magique"

Code :

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

1
2
3
UPDATE TS_Articles 
INNER JOIN TS_Transactions ON TS_Articles.No_Article = TS_Transactions.No_Article 
SET TS_Articles.Stock_Actuel = TS_Articles.Stock_Actuel+[Quantite];

Ici l'avantage c'est justement le non cumul préalable des données
surtout quant la requête est plus bien plus complexe (multiples relations + conditions WHERE )

Mais bon ! si cela est impossible sous SQl Server, je ferai des cumuls

Manuel

[Michel.Priori]

Bonsoir,

Ben merde, la requête fait bien ce que vous dites.
Ok.

On notera quand même que si on modifie (simplifie) la requête, on revient à un comportement "normal".

Code :

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

UPDATE TS_Articles INNER JOIN TS_Transactions ON TS_Articles.No_Article = TS_Transactions.No_Article SET TS_Articles.Stock_Actuel = 0+[Quantite];

Donc avec Access, c'est selon.

Edit : En fait non c'est "pire" que ça.
A l’exécution de la requête, Access nous prévient que 9 lignes vont être mises à jour (alors que la table TS_Articles n'en contient que 5).
La même ligne est donc mise à jour plusieurs fois ; la formule étant ré-entrante on obtient bien le résultat explicité (et donc pas si la formule ne l'est pas comme dans mon exemple)

En clair si dans la table à mettre à jour il n'existe qu'une ligne mais que le jeux des jointures nous en ramène 100, il y aura 100 écritures.
Waouh, bonjour les perf lors des montées en volume !

Comme je suis un vilain petit curieux j'ai encore fait une variante :

Code :

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

1
2
UPDATE TS_Articles INNER JOIN TS_Transactions ON TS_Articles.No_Article = TS_Transactions.No_Article SET TS_Transactions.Quantite = -1,  TS_Articles.Stock_Actuel = TS_Articles.Stock_Actuel+[Quantite]
WHERE (((TS_Transactions.No_Article)=1));

Vu qu'on met à jour la projection, on est syntaxiquement en droit de modifier n'importe quelle colonne de celle-ci.

La quantité de TS_Articles.Stock_Actuel est bien de -2 (avec le jeu d'exemple)
Le traitement est un curseur.

A noter que l'ordre des colonnes à mettre à jour est important ; en inversant on obtient 300 (avec le jeu d'exemple)

Merci sas68sas, je me coucherais moins bête ce soir

[Michel.Priori]

Bonjour,

Cette requête m'a turlupiné.

Si on reprend, syntaxiquement, la requête Access l'objet mis à jour n'est pas la table mais la projection.
Sous condition, il est possible de mettre à jour les tables par rapport à des projections (vues).

J'ai donc fait le test suivant :

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
create database test;
use test
go
 
drop table dbo.TS_articles;
go
create table dbo.TS_articles
(No_Cpte      int  
,No_Article   int not null unique
,Nom_Article  varchar(50)
,Stock_Actuel int
,CONSTRAINT PK_No_Cpte primary key (No_Cpte)
)
 
Insert into dbo.TS_articles values
 (11	,1	,'Art1'	,0 )
,(12	,2	,'Art2'	,0 )
,(13	,3	,'Art3'	,0 )
,(14	,4	,'Art4'	,0 )
,(15	,5	,'Art5'	,0 )
;
go
 
drop table dbo.Ts_Transaction
go
create table dbo.Ts_Transaction
(No_transaction    	int CONSTRAINT PK_No_Transaction primary key
,No_Article			int REFERENCES TS_articles(No_Article ) 
,Quantite 			int
)
 
insert into dbo.Ts_Transaction values
,(1,	1,	-1    )
,(2,	2,	2000  )
,(3,	2,	1000  )
,(4,	2,	400   )
,(5,	2,	600   )
,(6,	3,	150   )
,(7,	1,	-1    )
,(8,	3,	200   )
,(9,	5,	333   )
;
 
 
With pseudo_vue as
(select TS_Articles.No_Article, Stock_Actuel,[Quantite] 
from TS_Articles INNER JOIN TS_Transactions 
	ON TS_Articles.No_Article = TS_Transactions.No_Article
)
update pseudo_vue
set Stock_Actuel = Stock_Actuel+[Quantite]
;

Le résultat est ... 4 lignes mises à jour => ouf ! pas de mise à jour multiples de la même ligne.

[Michel.Priori]

Re,

Dans la mesure où :
- vous comparez SQL server à Access
- vous pratiquez la dénormalisation

Je me permet de vous indiquer que les vues (create view) sont l'équivalent des "requêtes".
A voir si, l'évaluation du "stock actuel" ne serait pas aussi/plus efficace que le re-calcul (et, en l'état de la description de vos tables, plus sûr) via une "vue indexée".

Si ce n'est pas le cas ou si cette solution ne vous convient pas, il est possible de créer des triggers (pas d'équivalent Access).
La dé-normalisation est l'une des justifications de la présence des triggers dans les SGBD.
Mais ça reste dangereux.
Je vous conseille vivement de renommer la colonne "stock actuel" avec une information indiquant que la mise à jour de celle-ce dépend d'un trigger. "Trig_Stock_Logique" par exemple.

Un des problème est la purge des données dans la table "transaction" (on ne peu pas envisager de garder les données indéfiniment).
En se reposant simplement sur la valeur du stock actuel, cela devient très compliqué de justifier les écarts de stock.
A minima, je vous conseille d'avoir un arrêté de l'état du stock logique à date avec un pointage manuel valant pour validation, et, de conditionner les purges à la date de dernière validation.

[sas68sas]

Bonjour,

Oui on peut décomposer le problème avec With … AS
mais le résultat n’est pas celui escompté.

Cette « requête » :

Code :

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

1
2
3
4
5
6
7
With pseudo_vue as
(select TS_Articles.No_Article, Stock_Actuel,[Quantite] 
 from TS_Articles INNER JOIN TS_Transaction 
 ON TS_Articles.No_Article = TS_Transaction.No_Article
)
Update pseudo_vue
set Stock_Actuel = Stock_Actuel+[Quantite];

Equivaut à :

Code :

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

1
2
3
4
update pseudo_vue
  Set pseudo_vue.Stock_Actuel = pseudo_vue.Stock_Actuel+[Quantite]
  From TS_Articles AS pseudo_vue INNER JOIN TS_Transaction 
	 ON pseudo_vue.No_Article = TS_Transaction.No_Article

Qui équivaut à (comme sous Access)

Code :

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

1
2
3
4
update TS_Articles
  Set TS_Articles.Stock_Actuel = TS_Articles.Stock_Actuel+[Quantite]
  From TS_Articles INNER JOIN TS_Transaction 
  ON TS_Articles.No_Article = TS_Transaction.No_Article

Qui donnent toutes le même résultat « erroné » Art1<>400 , art1<>-2




Le seul moyen d’obtenir le « bon résultat » c’est de faire la somme en regroupant :

Code :

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

1
2
3
4
5
6
7
8
9
With pseudo_vue as
(SELECT No_Article, Sum([Quantite]) AS QtesIN
 FROM TS_Transaction 
 GROUP BY No_Article
)
Update TS_Articles
 Set Stock_Actuel = Stock_Actuel+ pseudo_vue.QtesIN
 FROM TS_Articles INNER JOIN pseudo_vue
 ON TS_Articles.No_Article=pseudo_vue.No_Article;

Le résultat : Art1=400 , art1=-2


 
Autrement dit il n’existe pas de requête « façon Access » faisant le calcul
sans passer par un regroupement et une somme.
Mais rien de grave une fois l’information connue…

Pour entrer plus dans les détails, le traitement des transactions de stock
se fera effectivement avec des Trigger sur la table [TS_Transactions]

L’utilisateur pourra introduire par l’intermédiaire d’autres Tables
un ou des articles ou des nomenclatures (Ex. : N1 = 2xArt2 + 4xArt6 + 5xAr9)
dans la table [TS_Transactions] qui mettra à jour le total en stock des différents
articles selon l’ajout, le changement, la suppression des lots

D’où mes recherches et questions sur une requête mise à jour sans faire de total préalable.
Mais au final je fais des totaux…

TRIGGER sur insertion dans la table [TS_Transactions]

Code :

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

1
2
3
4
5
6
7
8
9
 WITH Source AS 
      ( SELECT inserted.No_Article_TT, Sum([Quantite_TT]) AS QtesIN
        FROM inserted 
        GROUP BY inserted.No_Article_TT
	     )
    UPDATE TS_Articles 
      SET TS_Articles.Stock_Actuel_TA =TS_Articles.Stock_Actuel_TA + Source.QtesIN
	     FROM TS_Articles INNER JOIN Source
	     ON TS_Articles.No_Article_TAN=Source.No_Article_TT;

Reste effectivement tôt ou tard la purge des données
de la table [TS_Transactions] sans toucher au niveau de stock

Je pense faire ça, le moment venu, en désactivant les triggers
de la table [TS_Transactions]