hirolau hirolau - 1 month ago 11
SQL Question

CTE and table update in ORACLE

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

Answer

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
         );