Support Questions

Find answers, ask questions, and share your expertise
Announcements
Celebrating as our community reaches 100,000 members! Thank you!

Is there an option to update a column in a table using values from another table in hive?

avatar
Expert Contributor

I want to update the joining date column in table x which is currently messed up, by using the start date column in table y. The schema for both the tables are different however the common column is the id field. I'm unable to use update statement since I can't mention a sub query in the where clause. So I tried using the following and I'm unable to update the column :

insert into table x (joining_date) select min(start_date) from table y group by id;

1 ACCEPTED SOLUTION

avatar
Expert Contributor

I wrote a join query to display the results I wanted by combining values from both the tables 'x' and 'y' (Manipulated the tables with the columns I wanted to see in the final output for 'x') and then did an insert overwrite into table 'x'. While creating the table into which you would like to overwrite (in my case it was 'x'), just make sure to remove the line "TBLPROPERTIES ('transactional'='true');" so it doesn't conform to ACID properties, else you won't be able to overwrite!

View solution in original post

6 REPLIES 6

avatar
Master Mentor

avatar
Expert Contributor

@Neeraj Sabharwal -- Thanks. But I found a way to overwrite my table with the query results that joined the tables and did the necessary manipulation I needed.

avatar
Master Mentor

@keerthana gajarajakumar please post your solution as answer so we could close this

avatar
Expert Contributor

I wrote a join query to display the results I wanted by combining values from both the tables 'x' and 'y' (Manipulated the tables with the columns I wanted to see in the final output for 'x') and then did an insert overwrite into table 'x'. While creating the table into which you would like to overwrite (in my case it was 'x'), just make sure to remove the line "TBLPROPERTIES ('transactional'='true');" so it doesn't conform to ACID properties, else you won't be able to overwrite!

avatar
Master Mentor

re-accepted so you can get points @keerthana gajarajakumar

avatar
Expert Contributor

haha alright 🙂