Thread: UPDATE COMPATIBILITY
What version of postgresql that the update compatibility below will be implemented? UPDATE COMPATIBILITY UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id= accounts.sales_id); TIA, -- Gera Mel E. Handumon ----------------------------------------------------------------- "Share your knowledge. It's a way to achieve immortality" - Dalai Lama
Gera Mel Handumon, 17.01.2012 07:31: > What version of postgresql that the update compatibility below will be > implemented? > > UPDATE COMPATIBILITY > > > UPDATE accounts SET (contact_last_name, contact_first_name) = > (SELECT last_name, first_name FROM salesmen > WHERE salesmen.id = accounts.sales_id); None as far as I know. You need to rewrite it to: UPDATE accounts SET contact_last_name = s.last_name, contact_first_name = s.first_name FROM salesmen s WHERE s.id = accounts.sales_id
On Tuesday, January 17, 2012 12:49:44 am Thomas Kellerer wrote: > Gera Mel Handumon, 17.01.2012 07:31: > > None as far as I know. > > You need to rewrite it to: > > UPDATE accounts > SET contact_last_name = s.last_name, > contact_first_name = s.first_name > FROM salesmen s > WHERE s.id = accounts.sales_id For completeness, you could also do: UPDATE accounts SET (contact_last_name,contact_first_name)= (s.last_name,s.first_name)FROM salesmen sWHERE s.id = accounts.sales_id Gets you a little closer to what you want:) -- Adrian Klaver adrian.klaver@gmail.com
Adrian Klaver, 17.01.2012 16:19: >> You need to rewrite it to: >> >> UPDATE accounts >> SET contact_last_name = s.last_name, >> contact_first_name = s.first_name >> FROM salesmen s >> WHERE s.id = accounts.sales_id > > For completeness, you could also do: > > UPDATE accounts > SET (contact_last_name,contact_first_name)= > (s.last_name,s.first_name) > FROM salesmen s > WHERE s.id = accounts.sales_id > Nice one!
On Tue, Jan 17, 2012 at 12:49 AM, Thomas Kellerer <spam_eater@gmx.net> wrote:
Gera Mel Handumon, 17.01.2012 07:31:None as far as I know.What version of postgresql that the update compatibility below will be
implemented?
UPDATE COMPATIBILITY
UPDATE accounts SET (contact_last_name, contact_first_name) =
(SELECT last_name, first_name FROM salesmen
WHERE salesmen.id = accounts.sales_id);
You need to rewrite it to:
UPDATE accounts
SET contact_last_name = s.last_name,
contact_first_name = s.first_name
FROM salesmen s
WHERE s.id = accounts.sales_id
You can put a query in the from clause instead of a table, I believe, too:
UPDATE accounts
SET contact_last_name = q.last_name,
contact_first_name = q.first_name
FROM (select last_name, first_name from salesmen where s.id = accounts.sales_id)
Not any more functional than the previous example, in this case, but very useful if you need a complicated join or aggregation/grouping.
Some useful info from the postgresql documentation on the UPDATE statement (http://www.postgresql.org/docs/9.0/static/sql-update.html ):
"When a FROM clause is present, what essentially happens is that the target table is joined to the tables mentioned in the fromlist, and each output row of the join represents an update operation for the target table. When using FROM you should ensure that the join produces at most one output row for each row to be modified. In other words, a target row shouldn't join to more than one row from the other table(s). If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable.
Because of this indeterminacy, referencing other tables only within sub-selects is safer, though often harder to read and slower than using a join."