question Re: Is there an option to update a column in a table using values from another table in hive? in Archives of Support Questions (Read Only)

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

keerthanakumar2 — Fri, 05 Feb 2016 12:39:09 GMT

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;

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

nsabharwal — Fri, 05 Feb 2016 12:55:59 GMT

@keerthana gajarajakumar

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

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

keerthanakumar2 — Fri, 05 Feb 2016 14:33:47 GMT

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

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

aervits — Fri, 05 Feb 2016 19:40:47 GMT

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

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

keerthanakumar2 — Sat, 06 Feb 2016 00:47:01 GMT

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!

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

aervits — Sat, 06 Feb 2016 01:45:36 GMT

re-accepted so you can get points @keerthana gajarajakumar

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

keerthanakumar2 — Sat, 06 Feb 2016 03:55:03 GMT

haha alright 🙂