Forum Discussion
arpost
Post Prodigy
Post ProdigyHow do you delete specific rows from a Lakehouse table?
Greetings, all. I have some files loaded into a Lakehouse that I've also loaded into a Lakehouse table. I want to delete some of the rows in the Lakehouse table, but I can't seem to find a way to do ...
Hi arpost ,
Thanks for using Fabric Community.It is possible to delete rows in a Lakehouse table using Spark-SQL in notebooks. I have created a repro of the scenario. I have attached the screenshots for your reference.
Trying to delete the row which has value of 46134
Query succeeded
After deletion of the row:
You can refer this link for more information: Link1
Hope this helps. Do let us know if you have any further issues. Glad to help.
TimoRiikonen
Advocate V
Advocate VI have the same problem: subqueries are not supported in delete.
I tried to create a temporary table and make a join, but that failed as well.
Since I have only two values, I can hard code my case for now.
Original deletion query:
DELETE FROM DE_LH_200_SILVER_Default.ServiceRequest WHERE updated NOT IN (select distinct DATE(updated) from DE_LH_100_BRONZE_Default.ServiceRequest)Failed attempt with temp table and join:
CREATE TABLE deletecontent
(
todelete DATE
);
INSERT INTO deletecontent
select distinct DATE(updated) from DE_LH_200_SILVER_Default.ServiceRequest WHERE updated NOT IN (select distinct DATE(updated) from DE_LH_100_BRONZE_Default.ServiceRequest);
SELECT * FROM deletecontent;
SELECT DISTINCT DATE(updated) FROM DE_LH_200_SILVER_Default.ServiceRequest INNER JOIN deletecontent ON deletecontent.todelete = DE_LH_200_SILVER_Default.ServiceRequest.updated;
DELETE FROM DE_LH_200_SILVER_Default.ServiceRequest SR INNER JOIN deletecontent ON deletecontent.todelete = DE_LH_200_SILVER_Default.ServiceRequest.updated;