is it possible to use existing query written with WITH clause as a source for multicolumn UPDATE statement? I have:
with x as ( rowid as rid, ... ), y as ( ... ), z as ( ... )
select x.rid, x.a ... z.a .. z.z
from x join y on x.a = y.a and .. x.z = y.z
y left join z on y.a = z.a and .. y.z = z.z
I would like to have:
update BIG_X
set ( a, b, .... z ) =
select [ appropriate columns from above with ] from ( [ here comes above with statement ] ) small_x
where BIG_X.rowid = small_x.rid
but it doesn't work. I found some examples for single-column update but is there any way to do multicolumn update without using MERGE statement (which does allow to use WITH as source subquery)?
thank you