Re: Using a CTE for an update

On 5/31/13 4:45 PM, Bosco Rama wrote:
> On 05/31/13 15:33, David Salisbury wrote:
>>
>> And without trying too much ;), I'll bet there is no way to do this in SQL proper.  i.e.
>> I can't correlate an update with a select stmt, as in a correlated sub-query sort of way.
>> So for this to work I would indeed need to write a function that "hard codes" the values
>> within the update stmt.
>
> I think this is what you want:
>
> update atmos_sites asites
>     set stationid = my.stationid
>     from my_stations my
>     where asites.id = my.atmos_site_id;
>
> HTH
>
> Bosco.

Thank you Bosco!  It passes syntax and later I'll have real data to work with.
This got me looking back in the "update" docs.  And here I was figuring it
had to be a CTE expression and didn't peruse the update section.
As a sub note, this part on the web is interesting.  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-valuedexpression,  
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.
-------------------


Happy Friday,

-ds