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 a field PARusing SUM(S_val)grouped by ID:

• For identifier 1 PARshould beSUM(SVAL) WHERE ID=1
• For identifier 2 PARshould beSUM(SVAL) WHERE ID=2

Expected Conclusion:

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 UPDATEvalue PAR?

My code is:

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

This does not work.

Answer 1

, , , MySQL.

:

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)

Answer 2

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

Answer 3

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

Check the entry. delete the line "FROM Table_Name".

Team

TRUE:

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