在 MySQL 中使用 SUM() 更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/653826/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
UPDATE with SUM() in MySQL
提问by venkatachalam
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 PARfield with the SUM(S_val), grouped by ID:
我需要PAR使用SUM(S_val)按 ID 分组的更新字段：
- For ID 1
PARshould beSUM(SVAL) WHERE ID=1 - For ID 2
PARshould beSUM(SVAL) WHERE ID=2
- 对于 ID 1
PAR应该是SUM(SVAL) WHERE ID=1 - 对于 ID 2
PAR应该是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 UPDATEthe PARvalue?
哪有我UPDATE的PAR价值？
My code:
我的代码：
UPDATE Table_Name SET PAR = (SELECT SUM(S_val) FROM Table_Name WHERE ID=1)
FROM Table_Name
This does not work.
这不起作用。
回答by Quassnoi
Unfortunately, you cannot update a table joined with itself in MySQL.
不幸的是，您无法更新在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)
回答by cjk
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
回答by Ali Alan
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.
检查写作。删除“FROM Table_Name”行。
TRUE command is:
真正的命令是：
UPDATE Table_Name SET PAR = (SELECT SUM(S_val) FROM Table_Name WHERE ID=1)
