Using the with clause, update the statement

[Onam]

I have a script that generates a result using a stack of with clauses, and I want to save that result in a table. I'm simply not getting it; could you put me in the proper direction?

Here's a basic illustration of what I'm looking for:

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

I'm at a loss for what to do. According to this blog, just update matched rows and copy the subselect from the SET clause into a WHERE EXISTS clause.

The real thing contains a lot of clauses that all relate to each other, so any recommendations that leverage the with clause instead of restructuring it to nested subqueries would be very appreciated.

 

[Onam]

I have a script that generates a result using a stack of with clauses, and I want to save that result in a table. I'm simply not getting it; could you put me in the proper direction?

Here's a basic illustration of what I'm looking for:

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

I'm at a loss for what to do. According to this blog, just update matched rows and copy the subselect from the SET clause into a WHERE EXISTS clause.

The real thing contains a lot of clauses that all relate to each other, so any recommendations that leverage the with clause instead of restructuring it to nested subqueries would be very appreciated.

(Ref Blog: https://www.scaler.com/topics/with-clause-in-sql/)