Help with MySQL Query

I want to perform a query on MySQL but I keep getting an error:

"#1111 - Invalid use of group function"

The query was already done for MS SQL Server, this is the query:

Code:

UPDATE products
SET stock=
(SELECT SUM(stock)
 FROM ILIL01
 WHERE ILIL01.lprod=products.code)
FROM products,ILIL01

Let me explain the involved tables, I have a table called ILIL01 with product code, stock and warehouse. The product codes are repeated many times because I can have 5 items of product xx on warehouse 1, 3 items of the same product xx on wharehouse 2 and so on...

I want to sum al the items from all warehouses for each product and then update that stock on another table called products where the product code is stored just one time and with the stock total.

This is easy to do in PHP but then I have to make a call to the database 3 times for each product (one SELECT DISTINCT to get all the codes just one time, another to sum the stock for each product, another to update the product table), so I end up doing like 33,000 calls to the database. Instead of accomplishing this with just one query like I had in MS SQL.

I'm not sure if this is the right forum for this type of question but would appreciate anyone's help!

Thanks

 

I think something like this may do it:

Code:

UPDATE products
SET stock =
(
 SELECT SUM(ILIL01.stock)
 FROM ILIL01
 WHERE ILIL01.lprod=products.code
);

Regards
Neil

 

Thanks for the reply Neil, that didn't work out though.

I got this error message:

 

Shouldn't it be

Code:

UPDATE products
SET stock =
(
 SELECT SUM(ILIL01.stock)
 FROM ILIL01[B][COLOR="Red"], products[/COLOR][/B]
 WHERE ILIL01.lprod=products.code
);

?

 

Nope... didn't work out either.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(ILIL01.stock)
FROM ILIL01, products
WHERE ILIL01....

 

Which Version of mySQL do you use? Because I think subeselect are supported since 4.0 / 4.1?

Isn't there a group by missing for aggregate functions like sum?
sth like