Databricks执行DELETE语句报错：不支持多列IN谓词条件

错误原因 #

你的DELETE语句里，子查询用了SELECT *，会返回表的所有列，但AWB_ID IN (...)只需要匹配单列的AWB_ID值，Databricks不支持多列值匹配IN条件，所以触发了AnalysisException错误。另外原语句的逻辑运算符优先级有问题，OR和AND混用没加括号，可能导致筛选逻辑不符合预期。

修正后的代码 #

from datetime import datetime, timedelta

today = datetime.today()
yesterday = today - timedelta(days=int(look_back_days))
yesterday_str = yesterday.strftime('%Y/%m/%d')

# 修正点1：子查询只选AWB_ID；修正点2：给OR条件加括号，明确逻辑优先级
query = f"""DELETE FROM table_name a 
WHERE AWB_ID IN (
    SELECT AWB_ID FROM a 
    WHERE (a.DW_CRT_RUN_DT >= '{yesterday_str}' OR a.dw_upd_run_dt >= '{yesterday_str}')
    AND a.awb_Stus_cd = 'VOID' 
    AND a.dw_upd_pgm_id = 'DSPD0580'
)"""

spark.sql(query)

备选方案（改用EXISTS子句） #

如果仍有报错，可改用EXISTS子句替代IN，逻辑更清晰，也能彻底避免多列匹配问题：

query = f"""DELETE FROM table_name a 
WHERE EXISTS (
    SELECT 1 FROM table_name b 
    WHERE a.AWB_ID = b.AWB_ID
    AND (b.DW_CRT_RUN_DT >= '{yesterday_str}' OR b.dw_upd_run_dt >= '{yesterday_str}')
    AND b.awb_Stus_cd = 'VOID' 
    AND b.dw_upd_pgm_id = 'DSPD0580'
)"""

spark.sql(query)

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