Re: [GENERAL] syntax for updating an aliased table
On Thu, May 26, 2011 at 1:40 PM, Rick Genter rick.gen...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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
Re: [GENERAL] 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 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
Re: [GENERAL] syntax for updating an aliased table
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
