sql - 用postgresql中每个id的列值总和更新表

Question

我有这样的表 A:

id     amount
1      2
1      3
2      0
1      4
2      1

我怎么能像这样更新它:(通过id计算amount的总和，删除重复行)

id  amount
1   9
2   1

我试过:

update a
set amount=sum(amount)
from a group by id;

出现语法错误，仍在处理中...

我实际使用的表是:

row_id | subject_id | hostay
----------------------------

我需要使用 sum(hostay) 更新 hostay 并通过其 subject_id 删除重复的行。

我试过的命令:

WITH cte AS (DELETE FROM ht returning *)
INSERT INTO ht (row_id,subject_id,hostay)
SELECT row_id,subject_id,SUM(hostay)
FROM cte 
GROUP BY row_id,subject_id;

并且重复的行仍然存在。

Answer 1

如果我理解正确的话，您实际上想要更改表中的记录。最简单的方法是删除并重新插入:

WITH cte AS (DELETE FROM your_table RETURNING *)
INSERT INTO your_table (id, amount)
SELECT id, SUM(amount)
FROM cte 
GROUP BY id