Thread: Re: Why is UPDATE with column-list syntax not implemented
AK wrote > 9.3 documentation says: > > According to the standard, the column-list syntax should allow a list of > columns to be assigned from a single row-valued expression, such as a > sub-select: > > UPDATE accounts SET (contact_last_name, contact_first_name) = > (SELECT last_name, first_name FROM salesmen > WHERE salesmen.id = accounts.sales_id); > This is not currently implemented — the source must be a list of > independent expressions. > > Why is this not implemented? Is it considered inconvenient to use, or > difficult to implement. or not important enough, or some other reason? I cannot answer why but I too would like to see this. I actually asked this a long while back but cannot seem to find my posting or recall the response. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779601.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Thu, Nov 21, 2013 at 3:50 PM, David Johnston <polobo@yahoo.com> wrote: >> Why is this not implemented? Is it considered inconvenient to use, or >> difficult to implement. or not important enough, or some other reason? > > I cannot answer why but I too would like to see this. I actually asked this > a long while back but cannot seem to find my posting or recall the response. At least on this case, rules could implement this. Though figuring out if whether it's the case or not might be harder.
Claudio, Can you elaborate how rules can help? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779896.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
On Fri, Nov 22, 2013 at 6:36 PM, AK <alkuzo@gmail.com> wrote: > Claudio, > > Can you elaborate how rules can help? Well... that specific example: > UPDATE accounts SET (contact_last_name, contact_first_name) = > (SELECT last_name, first_name FROM salesmen > WHERE salesmen.id = accounts.sales_id); Can be rewritten as UPDATE accounts SET contact_last_name = t.last_name, contact_first-name = t.first_name FROM (SELECT salesmen.id as salesmen_id, last_name, first_name FROM salesmen) t WHERE t.salesmen_id = accounts.sales_id; That's not 100% general, but it's quite general enough, transforming: UPDATE <T> SET (<field_list>) = (SELECT <field_list_b> <from_expr> WHERE <T>.<F> = <join_expr> <filter_expr>) Into UPDATE <T> SET <field_n = tmp.field_b_n for all n> FROM (SELECT <join_expr> AS <T>_<F>, <field_list_b> <from_expr> WHERE <filter_expr>) tmp WHERE <T>.<F> = tmp.<T>_<F>; That's *almost* a regex. It's possible the transformation can be done at the AST-level more generally, but I don't know enough of postgres parser to go deeper into that path, but the general idea being that it can be done even more generally with CTEs, if the where clause terms that relate to the updated table can be pinpointed and extracted into the CTE (as long as they're stable).
Claudio, Unfortunately, this UPDATE...FROM approach does not detect ambiguities, unless we go for tricks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5780215.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.