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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Announcements
FabricCarousel_June2024

Fabric Monthly Update - June 2024

Check out the June 2024 Fabric update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.