Hi Yogesh, i overlooked one thing and for completeness we should make note of it here.
change: -- non-intersected rows select a.* from TABLE_SQL a join NEW_BALS b on (a.key=b.key) where b.NEW_BALANCE is null to -- non-intersected rows select a.* from TABLE_SQL a *LEFT OUTER* join NEW_BALS b on (a.key=b.key) where b.NEW_BALANCE is null This takes care of the rows where code != 1 and code != 2. But you knew that! On Wed, Feb 19, 2014 at 8:33 PM, yogesh dhari <yogeshh...@gmail.com> wrote: > 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 >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >> >> >
