SQL With... Update

You can use merge, with the equivalent of your with clause as the using clause, but because you're updating the field you're joining on you need to do a bit more work; this:

merge into t42using (  select 1 as base, 10 as target  from dual) changeson (t42.id = changes.base)when matched thenupdate set t42.id = changes.target;

.. gives error:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "T42"."ID"

Of course, it depends a bit what you're doing in the CTE, but as long as you can join to your table withint that to get the rowid you can use that for the on clause instead:

merge into t42using (  select t42.id as base, t42.id * 10 as target, t42.rowid as r_id  from t42  where id in (1, 2)) changeson (t42.rowid = changes.r_id)when matched thenupdate set t42.id = changes.target;

If I create my t42 table with an id column and have rows with values 1, 2 and 3, this will update the first two to 10 and 20, and leave the third one alone.

SQL Fiddle demo.

It doesn't have to be rowid, it can be a real column if it uniquely identifies the row; normally that would be an id, which would normally never change (as a primary key), you just can't use it and update it at the same time.