Re: Using a CTE for an update

On 05/31/13 16:27, David Salisbury wrote:
>
> It would seem related to the above to me, but apparently it's not.
> -------------------
> 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.
> -------------------

You *could* use the single column version:

update atmos_sites asites
set stationid = (select my.stationid
from my_stations my
where my.atmos_site_id = asites.id);

Not sure how the performance would compare to the version mentioned in
the prior post though. Might be worth investigating as it would very
much depend on your schema and available indices.

HTH

Bosco.