Skip site navigation (1) Skip section navigation (2)

Re: update the same tuple in one command twice

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Kevin Grittner" <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: 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 16:25:07
Message-ID: 10254.1268756707@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"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

In response to

pgsql-bugs by date

Next:From: Robert HaasDate: 2010-03-16 18:29:18
Subject: Re: feature request: Loose index scans
Previous:From: Kevin GrittnerDate: 2010-03-16 14:08:08
Subject: Re: update the same tuple in one command twice

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group