Update statement using with clause

Question

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

User · Answer

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.

User · Answer

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

User · Answer

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

[sql] Update statement using with clause

Examples related to sql

Examples related to oracle