PostgreSQL UPDATE - query with left join problem

Question

UPDATE user
SET balance = balance + p.amount 
FROM payments p WHERE user.id = p.user_id AND p.id IN (36,38,40)

But it adds to the balance, only the value amount of the first payment 1936. Please help me how to fix it, i do not want to make cycle in the code to run a lot of requests.

Answer 1

In a multiple-table UPDATE, each row in the target table is updated only once, even it's returned more than once by the join.

From the docs:

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.

Use this instead:

UPDATE  user u
SET     balance = balance + p.amount
FROM    (
        SELECT  user_id, SUM(amount) AS amount
        FROM    payment
        WHERE   id IN (36, 38, 40)
        GROUP BY
                user_id
        ) p
WHERE   u.id = p.user_id