54

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.

starball
  • 20,030
  • 7
  • 43
  • 238
gjvdkamp
  • 9,929
  • 3
  • 38
  • 46
  • 2
    FYI: You don't have to edit your title and question to point out that you answered it yourself. Simply add your solution as its own answer and select it if it's the best one. – Wayne Mar 21 '11 at 17:25
  • Ok did that, can't mark it as answered yet. Thanks! – gjvdkamp Mar 21 '11 at 18:15
  • i was searching a response also and i found it here: http://stackoverflow.com/questions/7030699/oracle-sql-update-with-data-from-another-table – Bogdan Jul 30 '13 at 06:38

3 Answers3

64

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

gjvdkamp
  • 9,929
  • 3
  • 38
  • 46
  • 7
    Won't this update all the rows? If there are rows where c.id <> t.id, won't they be set to null? Does anyone knows a way to put that where clause outside too? – Sanjay Nambiar Apr 22 '13 at 05:29
  • 1
    Don't use Oracle anymore and this was a while back. Try it and let us know ;-) IIRC this worked for me. – gjvdkamp Apr 22 '13 at 07:47
  • 2
    @gjvdkamp it works just like Sanjay Nambiar said: the update works on selected rows, but all other rows that are out of "where" are set to null. [2] Does anyone knows a way to put that where clause outside too? – robsonrosa Sep 01 '15 at 20:40
  • A caveat for the with clause. It only works when setting a single value. (cola, colb) = (with ...) fails while colc = (with ...) works. Thanks for that oracle. The failure message is SQL Error: ORA-01767: UPDATE ... SET expression must be a subquery – Aaron Nov 21 '19 at 16:35
  • Ok I've stoppd using Oracle a long time ago. In MSSQL this would be a breeze, maybe you need a cursor in oracle. – gjvdkamp Nov 21 '19 at 19:18
  • 2
    @robsonrosa - To only update matching rows, you'd basically just copy the subselect from the SET clause into a WHERE EXISTS clause – BimmerM3 Jun 11 '20 at 23:21
  • @Aaron you can get around that by putting the query with the WITH clause into another subquery - something like this: UPDATE test_table a SET (a.test2, a.test3) = ( SELECT test2, test3 FROM ( WITH test_with AS ( SELECT * FROM test_table b WHERE test1 = 1 ) SELECT test_with.test1, 2 AS test2, 2 AS test3 FROM test_with ) c WHERE a.test1 = c.test1 ) – BimmerM3 Jun 11 '20 at 23:23
  • 1
    like what @SanjayNambiar said, I think this needs to be reverted somehow as not the answer, otherwise will causing issue for ppl not reading the comments – Justin Jun 30 '20 at 05:19
40

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.

Dave Costa
  • 47,262
  • 8
  • 56
  • 72
  • 1
    Hi, thanks a lot for your effort, already managed to get it to run. The with statements I use has quite a few levels that compute averages and stddevs, then uses that no normalize (statistically) data from other tabels etc. would be a real pain to refactor. The example was just the update syntax. – gjvdkamp Mar 21 '11 at 17:17
4

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
Yugesh
  • 91
  • 1
  • 6