sql - PostgreSQL:how to update rows in CTE -

am running postgresql 9.2.
below given sample of huge , uglyquery

with cte as( select ....... atable  ),cte1 ( select ..... btable inner join cte using(anid) ),update_cte as( update cte set afield=(select somthing cte1) ) select * cte 

need create view final result.

while executing above getting error below.

error:  relation "cte" not exist 

i know doing bad.
hope can understand trying achieve above query.
so please suggest alternative method it.

replies appreciated.

note : the actual query

with cte as( select ....... atable ),update_cte as( update cte set afield=(select somthing cte1) ) 

you can't that.

an update may not reference cte term in postgresql, ctes materialized. aren't views on underlying data. (that's annoying, that's how is).

you can:

create temporary view someview select ... atable;  update someview set afield = ... 

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

otherwise, think want like:

with cte1 (   select ..... btable inner join cte using(anid) ) update atable  set afield=(select somthing cte1) ... clause cte ... returning *; 

but really, please don't call cte terms cte, cte1, etc. give them useful, descriptive names tell are. it's programs full of variables named a through x ... next person has maintain code, or ask help, not it.