Re: update the same tuple in one command twice

From: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
To: <pgsql-bugs(at)postgresql(dot)org>,"terry" <94487509(at)qq(dot)com>
Subject: Re: update the same tuple in one command twice
Date: 2010-03-16 14:08:08
Message-ID: 4B9F4A78020000250002FDB8@gw.wicourts.gov
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

"terry" <94487509(at)qq(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2010-03-16 16:25:07 Re: update the same tuple in one command twice
Previous Message Peter Eisentraut 2010-03-16 11:02:08 Re: Error when lock conflict on REPLACE function