MYSQL Update using sum() result across multiple tables

Question

This bits working great:

 SELECT products_id, sum(attributes_stock) 
 FROM products_attributes 
 GROUP BY products_id

Which adds together all the groups of fields in the attributes_stock column.

What I am having trouble with is getting this result to UPDATE another column in another table.

This is what I have:

 UPDATE products, products_attributes 
 SET products.products_quantity = sum(products_attributes.attributes_stock) GROUP BY products_attributes.products_id 
 WHERE products.products_id = products_attributes.products_id

Any advice greatly appreciated.

score 30 · Accepted Answer · answered Jan 22 '13 at 23:43

You can't use a group by inside an update statement. You'll need to use an sub select to do the grouping.

Something like this:

UPDATE products p,( SELECT products_id, sum(attributes_stock)  as mysum
                   FROM products_attributes GROUP BY products_id) as s

   SET p.products_quantity = s.mysum
  WHERE p.products_id = s.products_id

score 8 · Answer 2 · answered Jan 23 '13 at 00:12

8

Some favor the newer-style JOIN ... ONsyntax for a join operation, vs. the comma operator and the join predicate in the WHERE clause:

UPDATE products p
  JOIN ( SELECT q.products_id
              , SUM(q.attributes_stock) AS sum_attr
           FROM products_attributes q
          GROUP BY q.products_id
       ) r
    ON r.products_id = p.products_id
   SET p.products_quantity = r.sum_attr

answered Jan 23 '13 at 00:12

spencer7593

106,611
15
112
140

...just don't forget the "where" clause if you don't want to update every row in the table. – SEoF Jan 23 '13 at 00:14
I should also add - the "JOIN ... ON" syntax can actually improve the performance of queries when joining 3 or more tables. – SEoF Jan 23 '13 at 00:19
1

Another advantage of the `JOIN` syntax is that if you want to also update products (set `quantity=0`) that have no stock at all, you could easily change the join to `LEFT JOIN`. – ypercubeᵀᴹ Jan 23 '13 at 16:23

score 1 · Answer 3 · answered Jan 22 '13 at 23:46

Try this:

update 
    products, 
    (select 
        products_id, sum(attributes_stock) as sumAttr
     from products_attributes
     group by products_id) as a
set
    products.products_cuantity = a.sumAttr
where
    products.products_id = a.products_id

MYSQL Update using sum() result across multiple tables

3 Answers3

Linked