sql - UPDATE with SUM() in MySQL

Question

My table:

ID  NAME COST  PAR  P_val  S_val
1   X    5     0    1      0
1   y    5     0    2      0
1   z    5     0    0      5
2   XY   4     0    4      4

I need to update the PAR field with the SUM(S_val), grouped by ID:

• For ID 1 PAR should be SUM(SVAL) WHERE ID=1
• For ID 2 PAR should be SUM(SVAL) WHERE ID=2

Expected ouput:

ID  NAME COST PAR  P_val  S_val
1   X    5     5   1      0
1   y    5     5   2      0
1   z    5     5   0      5     
2   XY   4     4   4      4

How can I UPDATE the PAR value?

My code:

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

This does not work.

Answer 1

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

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

DELIMITER $$

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

DELIMITER ;

UPDATE  table_name
SET     par = fn_get_sum(id)