Postgres Pro
Downloads
Home   >   mailing lists

Thread: update the same tuple in one command twice

update the same tuple in one command twice

From
"terry"
Date:
In one command, why can we update the same tuple for twice?
And the result is not predicated!!

Welcome to psql 8.3.3, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

TEST=# CREATE TABLE t1(a INT, b INT);
CREATE TABLE
TEST=# CREATE TABLE t2(c INT, d INT, e INT);
CREATE TABLE
TEST=# INSERT INTO t1 VALUES(1, 1);
INSERT 0 1
TEST=# INSERT INTO t1 VALUES(2, 3);
INSERT 0 1
TEST=# INSERT INTO t2 VALUES(2, 203, 212);
INSERT 0 1
TEST=# INSERT INTO t2 VALUES(2, 324, 1342);
INSERT 0 1
TEST=# INSERT INTO t2 VALUES(3, 342, 214);
INSERT 0 1
TEST=# update t1 set a = t2.d from t2 where a=t2.c;
UPDATE 1
TEST=# select * from t1;
  a  | b
-----+---
   1 | 1
 203 | 3
(2 rows)

why not is:
  a  | b
-----+---
   1 | 1
 324 | 3
(2 rows)

TEST=# select * from t2;
 c |  d  |  e
---+-----+------
 2 | 203 |  212
 2 | 324 | 1342
 3 | 342 |  214
(3 rows)

TEST=#

Re: update the same tuple in one command twice

From
"Kevin Grittner"
Date:
"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

Re: update the same tuple in one command twice

From
Tom Lane
Date:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> "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?

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