Multiple UPDATE ... FROM same row is not working

user_12209014 0
Viewed 162

I'm trying to do multiple update, but it works only for the first row.

I have table "users" with 2 records:

create table users
(
  uid        serial                                 not null
    constraint users_pkey
      primary key,
  balance    numeric                  default 0     not null
);


INSERT INTO public.users (uid, balance) VALUES (2, 100);
INSERT INTO public.users (uid, balance) VALUES (1, 100);

I try to UPDATE user "1" twice with the query, but it update only one time: balance for user "1" become "105", not "115"

update users as u
set balance = balance + c.bal
from (values (1, 5),
             (1, 10)
     ) as c(uid, bal)
where c.uid = u.uid;

Why it not updated for all rows from subquery?

sql-update join postgresql
2 Answers

The postgresql documentation gives no reason for this behaviour but does specify it.

Relevant quote

When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the from_list, 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 a SELECT with a GROUP BY to combine the rows before performing the update.

user_5686350

You need to aggregate in the inner query before joining:

update users as u
set balance = balance + d.bal
from ( 
  select uid, sum(bal) bal
  from ( values (1, 5), (1, 10) ) as c(uid, bal)
  group by uid
) d
where d.uid = u.uid;

Demo on DB Fiddle:

| uid | balance |
| --- | ------- |
| 2   | 100     |
| 1   | 115     |
user_106767160
Related