Update column with SUM from another table, PostgreSQL

Question

I try to update my users points based on a SUM from another table.

UPDATE users u
INNER JOIN
(
    SELECT user_id, SUM(user_score_for_answer) as total
    FROM answer_histories
    GROUP BY user_id, total
) a ON users.id = a.user_id
SET U.points = a.total

ERROR: syntax error at or near "INNER"

Gordon Linoff · Accepted Answer · 2021-09-18 23:19:46Z

2

In Postgres, the syntax looks like:

UPDATE users u
    SET points = a.total
FROM (SELECT user_id, SUM(user_score_for_answer) as total
      FROM answer_histories
      GROUP BY user_id
     ) a 
WHERE u.id = a.user_id;

edited Sep 18, 2021 at 23:19

answered Sep 22, 2020 at 12:04

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Correia Over a year ago

A good practice in SQL language is avoiding aggregate functions (such as SUM, COUNT, MAX, etc.) in the 'GROUP BY' clause, so 'total' is unnecessary in this example, i.e. 'GROUP BY user_id' is enough to achieve what you want. For more information, please have a look at: w3schools.com/sql/sql_groupby.asp

Jans · Accepted Answer · 2021-07-21 11:18:24Z

0

Gordon's answer is great, just getting small error - "aggregate functions are not allowed in GROUP BY" which can be fixed by simply removing total from GROUP BY.

*(I could not put this in comment due to insufficient reputation).

answered Jul 21, 2021 at 11:18

Jans

231 silver badge3 bronze badges

Collectives™ on Stack Overflow

Update column with SUM from another table, PostgreSQL

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related