CTE and table update in ORACLE

Question

I have a lot of complicated logic I want to run before I finally store the result by updating a column in a table. I am getting an error and have been able to get it down to:

with my_cte as
(
  select x,ix from y
)
update z
set mycol = (select x from my_cte where z.ix = my_cte.ix)

This however gives the error:

Error at line 4:
ORA-00928: missing SELECT keyword
set mycol = (select x from my_cte where z.ix = my_cte.ix)

Does this simply mean CTEs cannot be used with updates since the following query works fine:

update z
set mycol = (select x from y where y.ix = my_cte.ix)

Using version 12c Enterprise Edition Release 12.1.0.2.0

Edit:

After tackling this problem for a while the only way to get a reasonable performance was to use the MERGE clause instead (Still using CTEs as in the answers below).

merge into z using (
  with my_cte as (
    select x,ix from y
  )
)
on (
  my_cte.ix = z.ix 
)
when matched then
update set mycol = my_cte.x

CTE can still be used as update subquery like this: https://stackoverflow.com/a/39534514/603516 — Vadzim, Nov 16 '17 at 16:49

score 21 · Accepted Answer · answered Oct 31 '16 at 11:21

21

In Oracle, the CTE is part of the SELECT not the UPDATE:

update z
    set mycol = (
          with my_cte as (
             select x, ix
             from y
          )
          select x from my_cte where z.ix = my_cte.ix
         );

answered Oct 31 '16 at 11:21

Gordon Linoff

1,242,037
58
646
786

1

With this approach, how would you update multiple columns simultaneously? Wouldn't you have to run the same CTE multiple times? – Vince I Jun 28 '19 at 16:20
@VinceI . . . No. You can update multiple columns at once with a single subquery in Oracle. – Gordon Linoff Jun 28 '19 at 17:16
1

could you please demonstrate how that would be accomplished using an example similar to above? – Vince I Jun 30 '19 at 13:56
@VinceI . . . If you have a question, it should be asked as a *question*, not a *comment*. – Gordon Linoff Jul 01 '19 at 01:10
1

Works only for single column :-( `update t SET (mycol, mycol_2) = (WITH my_cte AS (...) select x,y ...)` fails with `ORA-01767: UPDATE ... SET expression must be a subquery` – Wernfried Domscheit Jan 12 '22 at 13:18

score 3 · Answer 2 · answered Oct 31 '16 at 11:54

3

If z.ix - is primary kay and y.ix - is foreign key to z.ix you may write

update (select y.x, z.mycol
             from y, z 
           where y.ix = x.ix)
    set mycol = x;

answered Oct 31 '16 at 11:54

Michael Piankov

1,989
1
8
19

CTE and table update in ORACLE

2 Answers2