ok. my conscience got the best of me. maybe for worse though. :)
This to me is like giving you a rope and a stool and i don't think it'll
end well.
That said consider something like this:
{code}
select
a.foo1,
a.foo2,
--column to be updated. you need to position it properly
--if null then no match. use value from table a
case
when z.sys_buscd_item_desc1 is null then
a.sys_buscd_item_desc1
--if not null then match. use value from table z
else
z.sys_buscd_item_desc1
end,
a.fooN_1,
a.fooN
from
(
select
a.sys_type_cd, -- join key
a.age, -- where clause
c.sys_buscd_item_desc1
from
TABLE1 a
join TABLE2 c on isnull(a.age,'00')=c.sys_item
where
c.sys_type_cd='AGE'
) z
RIGHT OUTER join TABLE1 a on (z.sys_z.sys_item = coalesece(a.age,'00') )
-- no where clause needed on 'AGE' since its part of the where clause in the
-- derived table.
{code}
i switched your ON clause and WHERE clause so be sure to take that under
consideration. And finally its not tested.
Best of luck.
Cheers,
Stephen
On Tue, Mar 4, 2014 at 7:49 AM, Stephen Sprague <sprag...@gmail.com> wrote:
>
> Let's just say this. Coercing hive into doing something its not meant to
> do is kinda a waste of time. Sure you can rewrite any update as a
> delete/insert but that's not the point of Hive.
>
> Seems like your going down a path here that's not optimal for your
> situation.
>
> You know, I could buy a Tesla and bury it in the ground and use it as a
> root cellar - but why?
>
> Cheers,
> Stephen
>
>
> On Mon, Mar 3, 2014 at 10:45 PM, yogesh dhari <yogeshh...@gmail.com>wrote:
>
>> Hello All,
>>
>> I have a use case in RDBMS query which I have implemented in
>> HIVE as..
>>
>>
>>
>> *1.1) Update statement* *in RDBMS*
>>
>> update TABLE1
>> set
>> Age= case when isnull(age,'') ='' then 'A= Current' else '240+ Days' end,
>> Prev_Age=case when isnull(prev_age,'') ='' then 'A= Current' else '240+
>> Days' end;
>> *1.2) Update statement* *in HIVE*
>>
>> create table TABLE2 as select
>> a.* ,
>> case when coalesce(a.age,'')='' then 'A=Current' else '240+ Days' end as
>> Age,
>> case when coalesce(a.prev_age,'')='' then 'A=Current' else '240+ Days'
>> end as Prev_age from TABLE1 a ;
>>
>>
>>
>>
>>
>> *Now I have a case statement in which I have a join condition*.
>>
>>
>>
>> *2) Join in RDBMS*
>> update TABLE1
>> set a.Age = c.sys_buscd_item_desc1
>> from TABLE1 a
>> join TABLE2 c
>> on c.sys_type_cd='AGE'
>> where isnull(a.age,'00')=c.sys_item;
>> commit;
>>
>>
>>
>>
>>
>> How can I implement this query into Hive, Pls help and suggest.
>>
>>
>>
>> Thanks In Advance
>>
>> Yogesh Kumar
>>
>>
>>
>>
>>
>>
>>
>
>
