gjvdkamp gjvdkamp - 1 year ago 85
SQL Question

update statement using with clause

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 eachother, so any suggestions actually using the with clause would be highly preferred over refactoring it to nested subqeuries.

Thanks in advance,


Answer Source

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

Good luck,