Re: 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(at)mcna(dot)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(dot)genter(at)gmail(dot)com