maybe consider something along these lines. nb. not tested.
-- temp table holding new balances + key
create table NEW_BALS as
select * from (
select b.prev as NEW_BALANCE, a.key from TABLE_SQL a join TABLE_SQL_2
b 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_3
b on (a.key=b.key) where a.code='2';
) z
;
-- i gave the table a new name but you could use TABLE_SQL instead of
NEW_TABLE_SQL for the true update.
insert overwrite table NEW_TABLE_SQL
select * from (
-- intersected rows - you'll have to line-up the columns correctly
substituting b.NEW_BALANCE into the right position
select a.col1, b.NEW_BALANCE, a.col2, ... from TABLE_SQL a join
NEW_BALS b on (a.key=b.key)
UNION ALL
-- non-intersected rows
select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where
b.NEW_BALANCE is null
) z
;
there's probably some typos in there but hopefully you get the idea and can
take it from here.
On Tue, Feb 18, 2014 at 4:39 AM, yogesh dhari <yogeshh...@gmail.com> wrote:
> Hello All,
>
> I have a use case where a table say TABLE_SQL is geting updated like.
>
>
> 1st Update Command
>
> update TABLE_SQL a
> set BALANCE = b.prev
> from TABLE_SQL_2 b
> where a.key = b.key and a.code = "1"
>
>
> 2nd Update Command
>
> update TABLE_SQL a
> set BALANCE = b.prev
> from TABLE_SQL_3 b
> where a.key = b.key and a.code = "2"
>
>
> same column is getting update twice in sql table,
>
> I have a Table in Hive say TABLE_HIVE.
>
> How to perform this kind operatation in HIVE,
>
> Pls Help,
>
> Thanks in Advance
> Yogesh Kumar
>
>
>
>
>
>
>
>
>
>
>
