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

[#1]

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;

[#2]

@keerthana gajarajakumar

See this https://cwiki.apache.org/confluence/display/Hive/LanguageManual+DML#LanguageManualDML-Update

[#3]

@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.

[#4]

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

[#5 · Accepted solution]

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!

[#6]

re-accepted so you can get points @keerthana gajarajakumar

[#7]

haha alright 🙂