I have a lot of complex logic that I want to run before I finally save the result by updating the column in the table. I get an error and got it:
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 an 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 mean that CTEs cannot be used with updates since the following request 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 solving this problem for some time, the only way to get reasonable performance was to use the MERGE clause instead (still using CTE, 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
source
share