On Tue, Jan 17, 2012 at 12:49 AM, Thomas Kellerer
<spam_eater@gmx.net> wrote:
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);
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.
"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."