syntax for updating an aliased table

The UPDATE statement when multiple tables are involved always drives me
nuts.

I think what you need to do is remove all of the "old." from the SET clause
and use "triple." in the WHERE clause instead of "old." - and remove the old
table alias from the UPDATE.

On Thu, May 26, 2011 at 9:38 AM, Andy Chambers <achambers@mcna.net> wrote:

I'm confused about the correct syntax for updating an aliased table. I
want to update triple from triple_updates
where the data is different and tried to use the following....

update triple old
set
old.obln = new.obln, old.ointv = new.ointv,
old.otime = new.otime, old.oflt = new.oflt, old.ostr = new.ostr,
old.oint = new.oint, old.oda = new.oda, old.uasid = new.uasid
from triple_update as new
where (old.s = new.s and
old.g = new.g) and
( old.obln <> new.obln or old.ointv <>
new.ointv or old.otime <> new.otime or old.oflt <> new.oflt or old.ostr
<> new.ostr or old.oint <> new.oint or old.oda <> new.oda or old.uasid <>
new.uasid)

...but postgres complains about not having column "old" in the triple
table. Putting an "as" between triple and old on the first line didn't make
any difference. If
I leave out the old alias, it complains about the columns being ambiguous.
How should the query above be changed to be syntactically correct?

Thanks,
Andy

--
Rick Genter
rick.genter@gmail.com

Andy Chambers wrote:

I'm confused about the correct syntax for updating an aliased table. I want
to update triple from triple_updates
where the data is different and tried to use the following....

update triple old
set
old.obln = new.obln, old.ointv = new.ointv,
old.otime = new.otime, old.oflt = new.oflt, old.ostr = new.ostr,
old.oint = new.oint, old.oda = new.oda, old.uasid = new.uasid
from triple_update as new
where (old.s = new.s and
old.g = new.g) and
( old.obln <> new.obln or old.ointv <>
new.ointv or old.otime <> new.otime or old.oflt <> new.oflt or old.ostr
<> new.ostr or old.oint <> new.oint or old.oda <> new.oda or old.uasid <>
new.uasid)

...but postgres complains about not having column "old" in the triple table.
Putting an "as" between triple and old on the first line didn't make any
difference. If
I leave out the old alias, it complains about the columns being ambiguous.
How should the query above be changed to be syntactically correct?

Don't use the table alias (or name for that matter) on the left-hand side of
the assignments, so:

update triple old
set obln = new.obln,
ointv = new.ointv,
...
from triple_update as new
where (old.s = new.s and old.g = new.g) and ...

The update statement already unambiguously defines what table is being updated
and the target columns are all that are necessary in the assignments.

HTH

Bosco.

On Thu, May 26, 2011 at 1:40 PM, Rick Genter <rick.genter@gmail.com> wrote:

The UPDATE statement when multiple tables are involved always drives me nuts.
I think what you need to do is remove all of the "old." from the SET clause and use "triple." in the WHERE clause instead of "old." - and remove the old table alias from the UPDATE.

This worked. Thanks very much

--
Andy