Spark : Subqueries are not supported in delete condition

[JayJay11]

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!

[v-nikhilan-msft]

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

[v-nikhilan-msft]

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

[JayJay11 · 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(...).