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-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.
-------------------

Happy Friday,

-ds