[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-general
Subject:    Re: [GENERAL] Using a CTE for an update
From:       David Salisbury <salisbury () globe ! gov>
Date:       2013-05-31 23:27:24
Message-ID: 51A931DC.7030903 () globe ! gov
[Download RAW message or body]




On 5/31/13 4:45 PM, Bosco Rama wrote:
> On 05/31/13 15:33, David Salisbury wrote:
> > 
> > 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.
> 
> I think this is what you want:
> 
> update atmos_sites asites
> set stationid = my.stationid
> from my_stations my
> where asites.id = my.atmos_site_id;
> 
> HTH
> 
> Bosco.

Thank you Bosco!  It passes syntax and later I'll have real data to work with.
This got me looking back in the "update" docs.  And here I was figuring it
had to be a CTE expression and didn't peruse the update section.
As a sub note, this part on the web is interesting.  It would seem related to
the above to me, but apparently it's not.


-------------------
According to the standard, the column-list syntax should allow a list of columns to \
be assigned from a single row-valued expression,  such as a sub-select:

UPDATE accounts SET (contact_last_name, contact_first_name) =
     (SELECT last_name, first_name FROM salesmen
      WHERE salesmen.id = accounts.sales_id);

This is not currently implemented — the source must be a list of independent \
                expressions.
-------------------


Happy Friday,

-ds


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[prev in list] [next in list] [prev in thread] [next in thread]