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
set SomeColumn = c.ComputedValue
from mytable t
inner join comp c on t.id = c.id
If anyone comes here after me, this is the answer that worked for me.
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 )