From: | David Salisbury <salisbury(at)globe(dot)gov> |
---|---|
To: | PostgreSQL general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Using a CTE for an update |
Date: | 2013-05-31 22:33:36 |
Message-ID: | 51A92540.8070707@globe.gov |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 5/31/13 4:21 PM, Jeff Janes wrote:
> On Fri, May 31, 2013 at 2:37 PM, David Salisbury <salisbury(at)globe(dot)gov <mailto: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
> <http://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
Thanks all.
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.
-ds
From | Date | Subject | |
---|---|---|---|
Next Message | Bosco Rama | 2013-05-31 22:45:37 | Re: Using a CTE for an update |
Previous Message | Jeff Janes | 2013-05-31 22:21:14 | Re: Using a CTE for an update |