Thread: UPDATE with value from another table

UPDATE [ ONLY ] table SET col = expression [, ...]
    [ FROM fromlist ]
    [ WHERE condition ]

Is this valid:

  UPDATE table1
  SET col = table2.col
  FROM table2
  WHERE col = table2.id;

On Sat, 2002-03-02 at 05:08, Thomas T. Thai wrote:
> UPDATE [ ONLY ] table SET col = expression [, ...]
>     [ FROM fromlist ]
>     [ WHERE condition ]
>
> Is this valid:
>
>   UPDATE table1
>   SET col = table2.col
>   FROM table2
>   WHERE col = table2.id;

Yes, in principle:

junk=# UPDATE table1
junk-#   SET col = table2.col
junk-#   FROM table2
junk-#   WHERE col = table2.id;
ERROR:  Column reference "col" is ambiguous

WHERE table2.col = table1.id;

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C

     "I will lift up mine eyes unto the hills, from whence
      cometh my help. My help cometh from the LORD, which
      made heaven and earth."
                           Psalms 121:1,2

On 2 Mar 2002, Oliver Elphick wrote:

> On Sat, 2002-03-02 at 05:08, Thomas T. Thai wrote:
> > UPDATE [ ONLY ] table SET col = expression [, ...]
> >     [ FROM fromlist ]
> >     [ WHERE condition ]
> >
> > Is this valid:
> >
> >   UPDATE table1
> >   SET col = table2.col
> >   FROM table2
> >   WHERE col = table2.id;
>
> Yes, in principle:
>
> junk=# UPDATE table1
> junk-#   SET col = table2.col
> junk-#   FROM table2
> junk-#   WHERE col = table2.id;
> ERROR:  Column reference "col" is ambiguous
>
> WHERE table2.col = table1.id;

right after i emailed the message, i tried it on a large dataset. works.
only gotcha is to drop any unique index first.

--
Thomas T. Thai
Minnesota.com, Inc.