Re: UPDATE COMPATIBILITY

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