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.

Thanks in advance,

Gert-Jan

3 Answers

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

The WITH syntax appears to be valid in an inline view, e.g.

UPDATE (WITH comp AS ... SELECT SomeColumn, ComputedValue FROM t INNER JOIN comp ...) SET SomeColumn=ComputedValue;

But in the quick tests I did this always failed with ORA-01732: data manipulation operation not legal on this view, although it succeeded if I rewrote to eliminate the WITH clause. So the refactoring may interfere with Oracle's ability to guarantee key-preservation.

You should be able to use a MERGE, though. Using the simple example you've posted this doesn't even require a WITH clause:

MERGE INTO mytable t
USING (select *, 42 as ComputedValue from mytable where id = 1) comp
ON (t.id = comp.id)
WHEN MATCHED THEN UPDATE SET SomeColumn=ComputedValue;

But I understand you have a more complex subquery you want to factor out. I think that you will be able to make the subquery in the USING clause arbitrarily complex, incorporating multiple WITH clauses.

You can always do something like this:

update mytable t
set SomeColumn = c.ComputedValue
from (select *, 42 as ComputedValue from mytable where id = 1) c
where t.id = c.id 

You can now also use with statement inside update

update mytable t
set SomeColumn = c.ComputedValue
from (with abc as (select *, 43 as ComputedValue_new from mytable where id = 1 select *, 42 as ComputedValue, abc.ComputedValue_new from mytable n1 inner join abc on n1.id=abc.id) c
where t.id = c.id