How does UPDATE FROM work?

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 ;

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.

StackExchange Database Administrators Q#335427, answer score: 11