Re: Using a CTE for an update

On Fri, May 31, 2013 at 2:37 PM, David Salisbury <salisbury(at)globe(dot)gov>wrote:

>
> I would think this would be possible. I'm on 9.0.8
>
> I have a reference between two tables, and want to populate a field in one
> table
> with a value that's in the referenced table ( based on the FK reference of
> course ).
>
> with row as ( select my.atmos_site_id, my.stationid from my_stations my,
> atmos_sites asites where my.atmos_site_id = asites.id )
> update atmos_sites set stationid = row.stationid where id =
> row.atmos_site_id;
>

This ability was introduced in 9.1.

"Allow WITH clauses to be attached to INSERT, UPDATE, DELETE statements
(Marko Tiikkaja, Hitoshi Harada)"

Also, you need a "from row" phrase on you update command.

Cheers,

Jeff