Forum Discussion

arpost's avatar
arpost
Icon for Post Prodigy rankPost Prodigy
2 years ago
Solved

How 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 ...
Need Help
pipelines
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

YOUNG99's avatar
YOUNG99
Frequent Visitor
2 years ago

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)

  • TimoRiikonen's avatar
    TimoRiikonen
    Icon for Advocate V rankAdvocate V
    2 years ago

    Right, "<", ">" and "=" type of comparisons work.

    Problem is only with the word "IN".