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

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

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

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

[sql] Update statement using with clause

Examples related to sql

Examples related to oracle