Re: update the same tuple in one command twice

"Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov> writes:
> "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?

I think maybe the OP meant to type "why *can't* we update the same tuple
twice?"

>> And the result is not predicated!!

> When you select multiple rows without specifying an order, the order
> cannot be reliably predicted.

Yeah. What's actually happening is that you get an update based on an
unspecified one of the join partners that the row-to-be-updated has.
(It happens to be the first one that the join process comes upon, but
that doesn't mean it's always physically first in the table.)

The reason you don't find this syntax in the SQL standard is exactly
that it's not terribly well-defined if there are multiple join partners.
We support it anyway because it's often useful, but you generally want
to make sure there is only one join partner.

regards, tom lane