CODEWITHSUNDEEP
hadoophiverdbmshiveql
Sanjiv
Sanjiv

Reputation: 1815

Multiple Table references in HIVE update statement

I have given update statement which works perfectly in RDBMS, but not working in HIVE. Currently In HIVE, you can't have reference of multiple tables(As in query TABLE TableA,TableB referenced) 

UPDATE A
FROM TableA A, TableB B
SET DepartmentId = B.DepartmentId
WHERE A.CustomerId = B.CustomerId ;

I need your help on How can I achieve the same in HIVE ? possible alternative in HIVE ?

Upvotes: 0

Views: 1253

Answers (2)

sunil
sunil

Reputation: 1279

Since Hive doesn't support row level inserts and updates there are few workarounds. The answer mentioned above is one such. One way would be to do the same thing and again insert overwrite into the same table.

INSERT OVERWRITE TABLE A SELECT A.c1,A.c2, ... , B.DepartmentId , .. FROM TableA A, TableB B WHERE A.CustomerId = B.CustomerId ;

This will be like updating the same table.

Upvotes: 1

www
www

Reputation: 4391

Hive tables are immutable. So update is not possible. You can always rewrite entire table: 

CREATE TABLE TableA_new
AS
SELECT A.c1,A.c2, ... , B.DepartmentId , .. 
FROM TableA A, TableB B
WHERE A.CustomerId = B.CustomerId ;

Answer is partially true, Update is available from HIVE 0.14 ;) GL

Upvotes: 0

Related Questions