title: CTE and table update in ORACLE
body: <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>