Обсуждение: syntax for updating an aliased table
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
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.
--
Rick Genter
rick.genter@gmail.com
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_updateswhere the data is different and tried to use the following....update triple oldsetold.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.uasidfrom triple_update as newwhere (old.s = new.s andold.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. IfI 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 insteadof "old." - and remove the old table alias from the UPDATE. This worked. Thanks very much -- Andy