Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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(...).
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(...).
Ask questions in Data Engineering, Data Science, Data Warehouse and General Discussion.
Check out the April 2024 Fabric update to learn about new features.