Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowGet certified as a Fabric Data Engineer: Check your eligibility for a 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700. Get started
I have 2 tables in the same lakehouse and one of them (existingData) has rows that need to be deleted, based on what's in the other table (newData). Whatever IDs found in newData are the rows that need to be deleted from existingData. The two tables have identical structure, but the IDs in newData are not continuous, so I cannot delete based on range. Also, subqueries are not supported in Spark SQL. Delete queries using inner join didn't work. I ended up using this PySpark loop but it's slow:
Solved! Go to Solution.
Hi @ebjim
I tried to repro the same scenario from my side. I have 2 tables with same schema in my lakehouse :
1) List with 40 rows
2) Listings_3 with 45 rows.
I tried to delete the 40 rows in Listings_3 with the below code:
The rows got successfully deleted. SQL MERGE is faster when compared to other DML operations. By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed.
Please try this approach and do let me know if you have any further questions. Hope this helps.
Hi @ebjim
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this. We will update you once we hear back from them.
Thanks
Hi @ebjim
I tried to repro the same scenario from my side. I have 2 tables with same schema in my lakehouse :
1) List with 40 rows
2) Listings_3 with 45 rows.
I tried to delete the 40 rows in Listings_3 with the below code:
The rows got successfully deleted. SQL MERGE is faster when compared to other DML operations. By using the MERGE statement, you can replace the individual DML statements with a single statement. This can improve query performance because the operations are performed within a single statement, therefore, minimizing the number of times the data in the source and target tables are processed.
Please try this approach and do let me know if you have any further questions. Hope this helps.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
42 | |
5 | |
2 | |
1 | |
1 |
User | Count |
---|---|
62 | |
9 | |
7 | |
6 | |
5 |