- If you have columns to update from the same table set, use column set updates.
- Use correlated subqueries for calculation of the values as well as for restriction of the rows to update. There you can employ an exists clause
DB2 is said to be able to recognise that both subselects are identical and therefore won't resolved twice. I suppose any clever database works like this.
update TO_BE_UPDATED U
set (COL_1, COL_2) = (
select S.ATT_3, R.ATT_4
from SOURCE_TABLE S
inner join RESTRICTING_TABLE R
on S.KEY_S = R.KEY_R
where R.SOME_CONDITION between SOME_VALUE and SOME_OTHER_VALUE
and S.KEY_S = U.KEY_U
)
where exists (
select S.ATT_3, R.ATT_4
from SOURCE_TABLE S
inner join RESTRICTING_TABLE R
on S.KEY_S = R.KEY_R
where R.SOME_CONDITION between SOME_VALUE and SOME_OTHER_VALUE
and S.KEY_S = U.KEY_U
)
