topic Re: Issue with Multi-column In predicates are not supported in the DELETE condition. in Machine Learning

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

cmilligan — Wed, 23 Nov 2022 20:43:30 GMT

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)

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

-werners- — Thu, 24 Nov 2022 10:12:48 GMT

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.

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

UmaMahesh1 — Sat, 03 Dec 2022 08:54:33 GMT

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..

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

shubhaskar — Wed, 17 Apr 2024 11:05:48 GMT

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

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

thisisthemurph — Thu, 14 Nov 2024 11:35:11 GMT

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") )

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

thisisthemurph — Thu, 14 Nov 2024 11:38:17 GMT

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.