5

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.

Community
  • 1
  • 1
venkatachalam
  • 102,353
  • 31
  • 72
  • 77
  • Although this has already been answered, you are asking for an inefficient way of doing things, with massive data duplication. Since you are aggregating the data by the IDs, there would be less data duplication if instead you created a second table which had `ID` as its primary key, and then stored the sum in that table. Then you could retrieve the sum when necessary, in a join. Since you will have to recalculate the data whenever any entry in the original table is updated, this way you will only update 1 entry per ID, not many. This approach is less error prone too. – cazort Mar 30 '22 at 18:19

3 Answers3

11

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)
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • 1
    You don't need to create a function. A sub-select works just fine. See http://stackoverflow.com/questions/14470041/mysql-update-using-sum-result-across-multiple-tables – Ian Oct 11 '14 at 14:57
  • @Ian: No it does not, not from the table you're updating. – Quassnoi Oct 11 '14 at 16:31
1

Try:

UPDATE Table_NAme SET PAR= summedValue
FROM   TAble_NAME t
JOIN (
  SELECT ID, SUM(S_val) as summedvalue 
  FROM TABLE_NAME GROUP BY ID
  ) s on t.ID = s.ID
cjk
  • 45,739
  • 9
  • 81
  • 112
1
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)
Edwin de Koning
  • 14,209
  • 7
  • 56
  • 74
Ali Alan
  • 19
  • 1
  • This only works because the 1 in the select expression is a constant and is not coordinated with the id value in the outer update. This restriction keeps you from updating all the rows in the table at once. – Don Jul 05 '13 at 15:21