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