CODEWITHSUNDEEP
hadoophivehiveql
Pragadeesh
Pragadeesh

Reputation: 113

Hive :Insert the records that are not present

I need to insert records into a table t1 from another table t2 such that insert only the records that are not in t2.

But when i use this query insert into table t1 select * from t2 where id not in (select id from t1);

But I get error as

Correlating expression cannot contain qualified column reference.

Can anybody suggest me a query to do this.

Upvotes: 1

Views: 1744

Answers (2)

Ashish Singh
Ashish Singh

Reputation: 533

You can also use below command :- 

insert into table t1 select t2.* from t2 left join t1 on t2.id=t1.id where t1.id is NULL;

Upvotes: 1

David דודו Markovitz
David דודו Markovitz

Reputation: 44951

t2.id

Yet another ridiculous hive limitation 

insert into table t1 select * from t2 where t2.id not in (select id from t1);

Upvotes: 1

Related Questions