Postgres Pro
Downloads
Home   >   mailing lists

Thread: syntax for updating an aliased table

syntax for updating an aliased table

From
Andy Chambers
Date:
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

Re: syntax for updating an aliased table

From
Rick Genter
Date:
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

Re: syntax for updating an aliased table

From
Bosco Rama
Date:
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.

Re: syntax for updating an aliased table

From
Andy Chambers
Date:
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
insteadof "old." - and remove the old table alias from the UPDATE. 

This worked.  Thanks very much

--
Andy