November 30, 2004 at 2:42 pm
I'm trying to run this update statement by I get an error saying that I am missing a field in the group by clause
Update EP set EP.Qtée = EP.Qtée + sum(C.FIPQTE - C.ANC_FIPQTE) + sum(C.qteRetour - C.ANC_qteRetour) from dbo.Contrat C inner join [Emplacement Pièce] EP on C.[No de produit] = EP.[No de produit] and C.Emplacement = EP.Emplacement and EP.[No de produit] > 0 and C.FFCONT = '041503....' group by C.FFCONT, EP.[no de produit], EP.Emplacement, EP.Qtée
but if I run this select statment I get no error
Select Qtée + sum(C.FIPQTE - C.ANC_FIPQTE) + sum(C.qteRetour - C.ANC_qteRetour) from dbo.Contrat C inner join [Emplacement Pièce] EP on C.[No de produit] = EP.[No de produit] and C.Emplacement = EP.Emplacement and EP.[No de produit] > 0 and C.FFCONT = '041503....' group by C.FFCONT, EP.[no de produit], EP.Emplacement, EP.Qtée
I tried grouping by all fields (even in the aggregates) but that didn't work either.
Any idea of what could be the problem?
November 30, 2004 at 2:49 pm
I found a workaround by redoing the join but I still don't understand what is causing the problem in the first update query I posted.
this works but I double the joining workload :
Update EPMain set EPMain.Qtée = dtQtée.newQtée from [Emplacement Pièce] EPMain inner join (Select EP.[no de produit], EP.Emplacement, EP.Qtée + sum(C.FIPQTE - C.ANC_FIPQTE) + sum(C.qteRetour - C.ANC_qteRetour) as newQtée from dbo.Contrat C inner join [Emplacement Pièce] EP on C.[No de produit] = EP.[No de produit] and C.Emplacement = EP.Emplacement and EP.[No de produit] > 0 and C.FFCONT = '041503....' group by C.FFCONT, EP.[no de produit], EP.Emplacement, EP.Qtée) dtQtée on dtQtée.[No de produit] = EPMain.[No de produit] and dtQtée.Emplacement = EPMain.Emplacement
November 30, 2004 at 4:20 pm
Not sure because I've never tried to do a group by in the primary update (it seems to me to be bad form). If I want aggregate info, then I do the aggregation in an embedded query--or prior temp table-- and join directly to that:
UPDATE a
SET col = w.val
FROM tablea a
JOIN ( SELECT keycol,...
, AVG(colname) as val
....
FROM tablea X
JOIN tableb Y on x.keyinfo = b.keyinfo
GROUP BY keycol,...
) w ON w.keycol = a.keycol
This makes it much more explicit what is going on.
November 30, 2004 at 9:58 pm
That's what I'm doing in my 2nd message. But I'm still wondering why the first form isn't working. Is it because the grouping messes with the primary key concept of the update (pk is composed of these 2 fields : [No de produit] and Emplacement) or is it just something that is not supported by design?
November 30, 2004 at 11:45 pm
I think it's not supported by design. I tried a similar update, and got the error message: "An aggregate may not appear in the set list of an UPDATE statement."
I think the actual problem is trying to update on a field that you are grouping by.
I'm sure your data does not have multiple rows for the group-by criteria:
EP.[no de produit], EP.Emplacement, EP.Qtée
However, it is a possible table design; and thus an update to the grouped item can't be done.
Bob Monahon
December 1, 2004 at 6:41 am
Thanx for the clarification.
One less gotcha to learn :-).
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply