CODEWITHSUNDEEP
hadoophivehiveql
forsaken
forsaken

Reputation: 709

Update column in hive table based on another table

I have a situation where I need to update a column's value based on certain conditions from another table. The data is as follows:

ID  Date   Amount
00  02/01  0
00  02/01  0
01  05/01  100
01  05/01  0

Another table contains the following:

ID  Date   Amount
00  02/01  0
00  02/01  0

I need to change the date column in the 2nd table to match the date value of ID '01' from the 1st table. I tried options of joining it but it does not seem to work correctly. What can be the easiest solution for it?

Upvotes: 0

Views: 5579

Answers (2)

Ravikumar
Ravikumar

Reputation: 1131

insert overwrite table table2 
select t1.id, 
       t2.Date,
       t2.amount 
from table2 t2 left join table t1 
     on t1.id=t2.id

In case if you are getting null value for ID missing in table1, you can include the when case 

insert overwrite table table2 
select case when(t1.id is null) then 0 else t1.id end, 
       t2.Date,
       t2.amount 
from table2 t2 left join table t1 
     on t1.id=t2.id

Hope this solve your problem.

Upvotes: 2

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can create a new table and drop the old one thereafter as updating tables isn't possible unless the table has transactional properties set. 

create new_table2 
location 'HDFS path' as 
select t2.id,d.date,t2.amount
from table2 t2 
cross join (select max(date) as date from table1 where id='01') d;
/*This assumes there is one distinct date for id=01 in table1*/

drop table table2 purge;

Upvotes: 0

Related Questions