Delete a record in Hive based on a table records in another database

0
votes

I have two hive databases namely db1 and db2. I have a table in db1 called table1, and a table in db2 called table2. I want to delete some rows in table2 based on particular column values in table1.

I use the below query but it fails

DELETE FROM db2.table2
 WHERE db2.table2.F_SESSION IN (
   SELECT F_SESSION FROM db1.table1 
      WHERE db1.table1.STATUS = 1);

The error is 

 Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 4:12 Invalid table alias or column reference 'db1': (possible column names are: f_session, status)

Any clue on where I am going wrong? Btw, I am not an experienced SQL person.

2
hivehiveql

2 Answers

0
votes

Try this,

DELETE FROM db2..table2
 WHERE db2..table2.F_SESSION IN (
   SELECT F_SESSION FROM db1..table1 
      WHERE db1..table1.STATUS = 1);
0
votes

But this worked

USE db2;
DELETE FROM table2 WHERE table2.F_SESSION IN (  
  SELECT F_SESSION FROM db1.table1 AS T1
      WHERE T1.STATUS = 1);