How do you delete specific rows from a Lakehouse table?

Appreciate it, Anonymous! I was hoping there was a way to do it through a UI, but that works.

I 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;

It doesn't work in a programmatic manner.

If you have pre-defined solution, then you can make the SQL command.
But if you for example create a table of keys and try to delete those rows, that is not possible atm.

Couple of options:
- Delete everything and recreate all of it without the lines you wanted to delete.

- Use Power Query filtering in dataflow and make the dataflow to overwrite the data.

CalebCarter927 

Give the below a try.  I won't take credit for it.  It was given to me by a MSFT trainer when I proposed the question.

----------------------------------------------------------

%%pyspark

df = spark.sql("SELECT MAX(DataDate) MDate FROM Lakehouse.Table")

MDate = df.agg({"MDate":"max"}).collect()[0]['max(MDate)']
#print(MDate)

sqlStr = "DELETE From Lakehouse.Table WHERE DataDate < '"+str(MDate)+"'"
#print(sqlStr)

#dfDistinct = spark.sql("SELECT DISTINCT DataDate FROM Lakehouse.Table")
#display(dfDistinct)

dml = spark.sql(sqlStr)