Help with MySQL Query

Discussion in 'Server Operation' started by jvilches, Aug 19, 2006.

  1. jvilches

    jvilches New Member

    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
     
  2. neil6179

    neil6179 New Member

    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
     
  3. jvilches

    jvilches New Member

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

    I got this error message:

     
  4. falko

    falko Super Moderator ISPConfig Developer

    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
    );
    ?
     
  5. jvilches

    jvilches New Member

    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....
     
  6. Ben

    Ben ISPConfig Developer ISPConfig Developer

    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
     

Share This Page