Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
JayJay11
Resolver II
Resolver II

Spark : Subqueries are not supported in delete condition

Hello all,

 

I want to perform a simple delete sql-statement on a delta table using the following code:

 

 

%%sql

DELETE
FROM LakehouseOperations.testFullData
WHERE DateInt NOT IN (SELECT DateInt From LakehouseOperations.testtecDataScope)

 

 

However, when executed I get the error:

 

"Subqueries are not supported in the DELETE (condition = (NOT (spark_catalog.LakehouseOperations.testFullData.DateInt IN (listquery()))))."

 

How can I achieve this?

 

According to the documentation below, this should be working: https://docs.databricks.com/en/sql/language-manual/delta-delete-from.html

 

Thanks!

1 ACCEPTED SOLUTION

I could solve it with Python using the join() function and "inner". Then I overwrite the existing delta table with write.mode("overwrite").format("delta").option("overwriteSchema", "true").save(...).

View solution in original post

3 REPLIES 3
v-nikhilan-msft
Community Support
Community Support

Hi @JayJay11 
Thanks for using Fabric Community.
Currently subquery in Delete condition is not supported with OSS delta lake. This is an open issue with delta lake: Delete and Update subquery support · Issue #826 · delta-io/delta · GitHub
The team is working to fix this. I will keep you posted regarding the updates.

Thanks

Hi @JayJay11 
We haven’t heard from you on the last response and was just checking back to see if your got resolved.
Otherwise, will respond back with the more details and we will try to help.
Thanks

I could solve it with Python using the join() function and "inner". Then I overwrite the existing delta table with write.mode("overwrite").format("delta").option("overwriteSchema", "true").save(...).

Helpful resources

This widget could not be displayed.