Re: update the same tuple in one command twice - Mailing list pgsql-bugs

"terry" <94487509@qq.com> wrote:

> In one command, why can we update the same tuple for twice?

> TEST=# update t1 set a = t2.d from t2 where a=t2.c;
> UPDATE 1

It says it updated it once.  Why do you think otherwise?

> And the result is not predicated!!

When you select multiple rows without specifying an order, the order
cannot be reliably predicted.  If you want the update to pick a
particular row as the source for the update, tell it which one.
Something like:

update t1 set a = t2.d from t2
  where a=t2.c
    and not exists
        (
          select * from t2 x
            where x.c = a
              and x.d > t2.d  -- or whatever logic you want here
        )
;

-Kevin