Hello Stephen , Yes, actully I have used Left Outer Join instead of Join, there were left outer joins in RDBMS Query instead of join.
Thanks again :) On Thu, Feb 20, 2014 at 10:45 AM, Stephen Sprague <sprag...@gmail.com>wrote: > 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 >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>>> >>> >>> >> >
