maybe consider something along these lines. nb. not tested.create table NEW_BALS as select * from ( select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2b on (a.key=b.key) where a.code='1'; UNION ALL select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_3b on (a.key=b.key) where a.code='2'; ) z ;

NEW_TABLE_SQL for the true update.

insert overwrite table NEW_TABLE_SQL

select * from (

substituting b.NEW_BALANCE into the right position select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a joinNEW_BALS b on (a.key=b.key)

UNION ALL

select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) whereb.NEW_BALANCE is null

) z;

there's probably some typos in there but hopefully you get the idea and cantake it from here.