Hi everybody,
I'd like to ask whether you could imagine that it would be a good enhancement to SQL to have the possibility to use the with clause together with the update statement?
What I'm coming across frequently is that I need to repeat a subquery over and over again when trying to calculate values for an update. So I would suggest to have a possibility to write something in the line of
<pre>with vals as (select key, col1, col2, col3 from some_table
update table_foo f
set col1 = (select col1 from vals where f.key = vals.key),
col2 = (select col1 from vals where f.key = vals.key)
where col3 in (select col3 from vals);</pre>
What do you think? Or: Am I missing the point here and an easier way of doing this is avialable already?
Best regards
Jürgen