CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
user3814846
user3814846

Reputation:

PostgreSQL:how to update rows in CTE

am running PostgreSQL 9.2.
below given is a sample of my huge and uglyquery 

with cte as(
select ....... from aTable 
),cte1 as (
select ..... from bTable inner join cte using(anID)
),update_cte as(
update cte set aField=(select somthing from cte1)
)
select * from cte


i need to create a view with the final result.

while executing the above am getting an error which is below.

ERROR:  relation "cte" does not exist

I know am doing something bad.
hope you can understand what am trying to achieve from the above query.
So please suggest any alternative method for it.

Replies are much appreciated.

Note : the actual query

Upvotes: 9

Views: 6999

Answers (1)

Craig Ringer
Craig Ringer

Reputation: 324475

with cte as(
select ....... from aTable
),update_cte as(
update cte set aField=(select somthing from cte1)
)

You can't do that.

An UPDATE may not reference a CTE term in PostgreSQL, as CTEs are materialized. They aren't just views over the underlying data. (That's sometimes really annoying, but that's how it is).

You can:

CREATE TEMPORARY VIEW someview AS SELECT ... FROM atable;

UPDATE someview SET afield = ...

if you want; that'll work on newer PostgreSQL versions that support automatically updatable views. I think 9.2 does.

Otherwise, I think you want something like:

WITH cte1 as (
  select ..... from bTable inner join cte using(anID)
)
update aTable 
set aField=(select somthing from cte1)
WHERE ... where clause from cte ...
RETURNING *;

but really, please don't call your CTE terms cte, cte1, etc. Give them useful, descriptive names that tell you what they are. It's like programs full of variables named a through x ... the next person who has to maintain your code, or anyone you ask for help, will not like it.

Upvotes: 7

Related Questions