配置单元 : Multiple In-elements with single subquery

Question

我试图在配置单元(版本 1.2.1)上运行这样的查询:

DELETE  FROM employee as e WHERE  (e.id, e.name) IN ( SELECT emp.id, emp.name FROM employee_final  emp) AND e.sno = 120 ;

但由于配置单元不支持这种构造，所以我尝试了这个:

DELETE FROM employee WHERE 
id  IN (  SELECT emp.id  FROM employee_final AS  emp ) 
AND 
name IN ( SELECT emp.name  FROM employee_final AS  emp      )  
AND e.sno = 120 ;

但是 hive 似乎有一个限制，它只支持 1 个子查询，我得到了这样的异常

Error Message : Grammatically incorrect SQL Query :]; nested exception is org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10249]: Line 1:185 Unsupported SubQuery Expression 'name': Only 1 SubQuery expression is supported.

处理这个问题最有效的方法是什么。

Answer 1

你可以试试看

1. 通过 EXISTS 我不确定 HIVE 是否支持这种形式的子查询。

   DELETE FROM employee as e WHERE EXISTS ( SELECT 1 FROM employee_final emp where e.id = emp.id and e.name =  emp.name ) AND e.sno = 120 ;
   

2. 通过CONCAT函数

   DELETE FROM employee as e WHERE concat (e.id, '#' ,e.name) IN ( SELECT concat (emp.id,'#' ,emp.name) FROM employee_final emp) AND e.sno = 120 ;