Update statement using with clause

Question

I have a script that uses a stack of with clauses to come up with some result, and then I want to write that result in a table. I just can't get my head around it, could someone point me in the right direction?

Here's a simplified example that indicates what i want to do:

with comp as (   select *, 42 as ComputedValue from mytable where id = 1 ) update  t set     SomeColumn = c.ComputedValue from    mytable t         inner join comp c on t.id = c.id  

The real thing has quite a few with clauses that all reference each other, so any suggestions actually using the with clause would be highly preferred over refactoring it to nested subqueries.

Thanks in advance,

Gert-Jan

Answer 1

If anyone comes here after me, this is the answer that worked for me.

NOTE: please make to read the comments before using this, this not complete. The best advice for update queries I can give is to switch to SqlServer ;)

update mytable t set z = (   with comp as (     select b.*, 42 as computed      from mytable t      where bs_id = 1   )   select c.computed   from  comp c   where c.id = t.id ) 

Good luck,

GJ