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 <achamb...@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.gen...@gmail.com
