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))
set counter = counter + duplicatedids.v
where verify_seq_update.id <= duplicatedids.idx ;