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:
PAR
should be SUM(SVAL) WHERE ID=1
PAR
should be SUM(SVAL) WHERE ID=2
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?
UPDATE Table_Name SET PAR = (SELECT SUM(S_val) FROM Table_Name WHERE ID=1)
FROM Table_Name
This does not work.
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)
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With