Re: Using a CTE for an update

On Sat, Jun 1, 2013 at 7:37 AM, David Salisbury <salisbury@globe.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;
>
> I get a syntax error at "update".  Seems updates don't act quite like
> selects.
>
> Do I need to write a function that iterates to do an update like this?

Similar statements work for me. When I try yours, I get a complaint
about relations not existing, which (based on my testing) seems to
imply that the syntax is fine. How are you invoking it? Maybe there's
a problem with the line break (eg if psql is told to terminate
statements with end-of-line)?

ChrisA