Thread: Using a CTE for an update

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?

thanks in advance,

-ds

On Sat, Jun 1, 2013 at 7:37 AM, David Salisbury <salisbury@globe.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

On 05/31/13 14:37, David Salisbury wrote:
>
> I would think this would be possible.  I'm on 9.0.8

IIRC, updatable CTE's don't appear until 9.1.x

HTH

Bosco.

On 5/31/13 4:21 PM, Jeff Janes wrote:
> On Fri, May 31, 2013 at 2:37 PM, David Salisbury <salisbury@globe.gov <mailto:salisbury@globe.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

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.

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

On 05/31/13 16:27, David Salisbury wrote:
>
> 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.
> -------------------

You *could* use the single column version:

update atmos_sites asites
    set stationid = (select my.stationid
                        from my_stations my
                        where my.atmos_site_id = asites.id);

Not sure how the performance would compare to the version mentioned in
the prior post though.  Might be worth investigating as it would very
much depend on your schema and available indices.

HTH

Bosco.