Delta Table执行带关联查询的DELETE操作出现语法错误如何解决？

错误原因 #

• Delta Lake的DELETE语法不支持直接在删除语句中使用JOIN关联其他表，你写的JOIN写法不符合Delta的语法规范
• 你的子查询存在笔误：SELECT col1, col2, col2重复查询了两次col2，缺少需要关联的col3字段，会导致关联时找不到aux.col3

推荐解决方案（USING子句实现关联删除） #

Delta官方支持通过USING子句引入关联数据集，写法如下：

DELETE FROM table1 inv
USING (
  -- 修正了原查询中重复col2的笔误，补全col3
  SELECT col1, col2, col3, min(Date) minDate, max(Date) maxDate 
  FROM table2 a 
  GROUP BY col1, col2, col3
) aux
WHERE 
  -- 关联条件
  aux.col1 = inv.col1 
  AND aux.col2 = inv.col2 
  AND aux.col3 = inv.col3 
  -- 过滤条件
  AND inv.Date BETWEEN aux.minDate AND aux.maxDate

可选轻量解决方案（IN子查询） #

如果关联逻辑简单、数据量小，也可以用IN子查询实现：

DELETE FROM table1
WHERE (col1, col2, col3, Date) IN (
  SELECT inv.col1, inv.col2, inv.col3, inv.Date
  FROM table1 inv
  JOIN table2 a 
    ON a.col1 = inv.col1 AND a.col2 = inv.col2 AND a.col3 = inv.col3
  GROUP BY inv.col1, inv.col2, inv.col3, inv.Date
  HAVING inv.Date BETWEEN min(a.Date) AND max(a.Date)
)

操作提示 #

执行删除前可将DELETE替换为SELECT count(*)，验证匹配行数和你之前查询的500-1000条一致后再执行删除，避免误操作。

内容的提问来源于stack exchange，提问作者Tony