6

When trying to run this query, my assumption was that PostgreSQL will run the update twice on a row with id=0 having initial counter=0 and would end up with a counter=11 but it actually runs only once and results in counter=6.

Can someone please help figure out why? I have spent a good amount of time attempting to figure this out and can't seem to figure out why this is.

with duplicatedids as 
  ((select 0 as idx, 6 as v) union 
   (select 1 as idx, 5 as v))
update  verify_seq_update 
set counter = counter + duplicatedids.v 
from duplicatedids 
where verify_seq_update.id <= duplicatedids.idx ;
1
  • 2
    Btw, use WITH duplicated_ids(idx, v) AS (VALUES (0, 6), (1, 5)) instead of the complicated UNION of single-row SELECTs
    – Bergi
    Feb 1 at 22:43

1 Answer 1

11

An UPDATE can modify many rows but each of them will be modified only once.

For a more detailed explanation, see the UPDATE section of Postgres documentation:

Notes
... 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.