CODEWITHSUNDEEP
hadoophive
MysticForce
MysticForce

Reputation: 1321

Delete table from hive not working

I want to delete rows from table A whose column c is greater than max of column c of table B

I tried

delete * from A
where A.p >= (select max(t.c) from B t)

but it is not working . How can I achieve this?

Upvotes: 0

Views: 661

Answers (2)

janeshs
janeshs

Reputation: 813

There are a couple of issues here:

  1. DELETE support itself is available starting from Hive 0.14 only.

  2. The DELETE syntax should not contain asterisk ("*"). Refer the official Hive Delete syntax.

  3. Also as rightly pointed out by MysticForce, Subqueries in where clause are only supported with IN, NOT IN, EXISTS, NOT EXISTS statements. Please refer Subqueries syntax in manual.

Upvotes: 1

Kishore
Kishore

Reputation: 5891

The INSERT ... VALUES, UPDATE, and DELETE statements require the following property values in the hive-site.xml configuration file:

hive.enforce.bucketing               true

hive.exec.dynamic.partition.mode    nonstrict

After updating the configuration in the corresponding hive-site.xml, restart the services – HiveServer2 and Hive Metastore.

then, use this hql

delete from A
where A.p >= (select max(t.c) from B t)

Upvotes: 1

Related Questions