On 5/31/13 4:21 PM, Jeff Janes wrote:
On Fri, May 31, 2013 at 2:37 PM, David Salisbury <salisbury@xxxxxxxxx <mailto:salisbury@xxxxxxxxx>> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
