CTE and table update in ORACLE

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;

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