CTE and table update in ORACLE
<p>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:</p>

<pre><code>with my_cte as
(
  select x,ix from y
)
update z
set mycol = (select x from my_cte where z.ix = my_cte.ix)
</code></pre>

<p>This however gives the error:</p>

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

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

<pre><code>update z
set mycol = (select x from y where y.ix = my_cte.ix)
</code></pre>

<p>Using version 12c Enterprise Edition Release 12.1.0.2.0</p>

<p>Edit: </p>

<p><em>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).</em></p>

<pre><code>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
</code></pre>