Update header table with sum detail table - Please help! 1

[southbeach]

I have read

http://dev.mysql.com/doc/refman/5.0/en/join.html

but cannot see how to apply a JOIN to an UPDATE ...

I am trying to update a header table with the sum from a second table. Here is my query

UPDATE products SET
products.prodReserved = SUM(productsd.pdQtyRes),
products.prodOnHand = SUM(productsd.pdQtyOnh),
products.prodAvailable = SUM(productsd.pdQtyAvl)
WHERE products.prodDicID = productsd.pdDicID

I am getting an error

SQL query:

UPDATE products,
productsd SET products.prodReserved = SUM( productsd.pdQtyRes ) ,
products.prodOnHand = SUM( productsd.pdQtyOnh ) ,
products.prodAvailable = SUM( productsd.pdQtyAvl ) WHERE products.prodDicID = productsd.pdDicID

MySQL said: Documentation
#1111 - Invalid use of group function

Searched for error 1111 but got so many results ... after scanning through them, none are related to what I am trying to do.

How can I do what I am attempting to?

Thanks!


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.

 

[AdaHacker]

The JOIN by itself looks OK, but I don't think you can use a JOIN and an aggregate function like that.

Try using correlated subqueries instead:

UPDATE products SET
products.prodReserved = (SELECT SUM(productsd.pdQtyRes) FROM productsd WHERE productsd.pdDicID = products.prodDicID),
products.prodOnHand = (SELECT SUM(productsd.pdQtyOnh) FROM productsd WHERE productsd.pdDicID = products.prodDicID),
products.prodAvailable = (SELECT SUM(productsd.pdQtyAvl) FROM productsd WHERE productsd.pdDicID = products.prodDicID)

 

[southbeach]

AdaHacker,

Thank you so much for replying so fast and with such interesting sample.

Your snippet worked great!

Thanks again ...


--
SouthBeach
The good thing about not knowing is the opportunity to learn - Yours truly, 2008.