MYSQL Update using sum() result across multiple tables

2 Answers

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

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.

UPDATE with SUM() in MySQL

Unfortunately, you cannot update a table joined with itself in MySQL.

You'll need to create a function as a workaround:


CREATE FUNCTION `fn_get_sum`(_id INT) RETURNS int(11)
      DECLARE r INT;
      SELECT  SUM(s_val)
      INTO    r
      FROM    table_name
      WHERE   id = _id;
      RETURN r;
END $$


UPDATE  table_name
SET     par = fn_get_sum(id)

UPDATE Table_Name SET PAR = (SELECT SUM(S_val) FROM Table_Name WHERE ID=1) 
FROM   Table_Name

Check writing. delete "FROM Table_Name" row.

TRUE command is:

UPDATE Table_Name SET PAR = (SELECT SUM(S_val) FROM Table_Name WHERE ID=1)