Using the with clause, update the statement

Joined
Jun 29, 2022
Messages
28
Reaction score
0
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:
Code:
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.
 
Joined
Jun 29, 2022
Messages
28
Reaction score
0
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:
Code:
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/)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,818
Messages
2,569,727
Members
45,661
Latest member
NadineBour

Latest Threads

Top