Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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(...).
User | Count |
---|---|
5 | |
4 | |
2 | |
1 | |
1 |
User | Count |
---|---|
14 | |
6 | |
5 | |
4 | |
3 |