gjvdkamp gjvdkamp - 6 months ago 31
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,

Gert-Jan

Answer

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,

GJ