sqloracle-database

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

Solution

  • If anyone comes here after me, this is the answer that worked for me.

    NOTE: please make to read the comments before using this, this not complete. The best advice for update queries I can give is to switch to SqlServer ;)

    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