Issue with Multi-column In predicates are not supported in the DELETE condition.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-23-2022 12:43 PM
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)
- Labels:
-
Delete
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-24-2022 02:12 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
12-03-2022 12:54 AM
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..
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-17-2024 04:05 AM
Had the same issue. Please check the subquery returned value there must be something wrong with that.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-14-2024 03:35 AM
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")
)
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
11-14-2024 03:38 AM
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.
![](/skins/images/97567C72181EBE789E1F0FD869E4C89B/responsive_peak/images/icon_anonymous_message.png)
![](/skins/images/97567C72181EBE789E1F0FD869E4C89B/responsive_peak/images/icon_anonymous_message.png)