Re: Using a CTE for an update

From: Chris Angelico <rosuav(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Using a CTE for an update
Date: 2013-05-31 21:51:17
Message-ID: CAPTjJmpcXN+SKopotQj840o-S0r7p_0GSk8BY9Gw=HEYW11DHw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, Jun 1, 2013 at 7:37 AM, 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;
>
> 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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Bosco Rama 2013-05-31 21:57:40 Re: Using a CTE for an update
Previous Message David Salisbury 2013-05-31 21:37:12 Using a CTE for an update