Issue with Multi-column In predicates are not supported in the DELETE condition.

[cmilligan]

I'm trying to delete rows from a table with the same date or id as records in another table. I'm using the below query and get the error 'Multi-column In predicates are not supported in the DELETE condition'.

delete from cost_model.cm_dispatch_consolidated
 
where delivery_date in (select delivery_date from cost_model.cm_dispatch_stats)
    or order_nbr in (select order_nbr from cost_model.cm_dispatch_stats)

What's strange is I do this against another table with no issues. Below is the one that is running fine.

delete from cost_model.cm_order_consolidated 
 
where delivery_date in (select delivery_date from cost_model.cm_order_stats)
    or order_nbr in (select order_nbr from cost_model.cm_order_stats)

[-werners-]

strange error message as it is supported, as you already noticed.

I'd check what the subqueries return, my guess is that the issue might be there.

[UmaMahesh1]

It might be because of nulls in the column you are defining. Can you please check if you have nulls in the working table and in the non working table and get back ?

Cheers..

[shubhaskar]

Had the same issue. Please check the subquery returned value there must be something wrong with that.

[thisisthemurph]

I seem to get this error on some DeltaTables and not others:

df.createOrReplaceTempView("channels_to_delete")

spark.sql("""
    delete from lake.something.earnings
    where TenantId = :tenantId
    and ChannelId = in (
        select ChannelId
        from channels_to_delete
    )
    """,
    args=dict(tenant_id="some_id")
)

[thisisthemurph]

I have an error in my query here where i am using tenant_id and :tenantId, this error does not exist in my actual code.