Thanks a lot Stephen Sprague :) :)
It worked.. , just to remove the " ; " from here, bcoz it was throuig
sub query systax error...
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
;
to
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
;
Thanks & Regards
Yogesh Kumar
On Tue, Feb 18, 2014 at 9:18 PM, Stephen Sprague <sprag...@gmail.com> wrote:
> 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
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>>
>
>
