• Welcome, Guest. Please login.
 
October 21, 2019, 03:26:21 am

News:

Welcome to the SQLitening support forums!


How do I find and Delete Duplicates?

Started by Andrew Lindsay, November 28, 2009, 04:56:11 am

Previous topic - Next topic

Andrew Lindsay

I have a table that has a DateTime field and an Integer field.  I want to I have imported (approximately) 4 GB of CSV text into the database, but now find that there are a considerable (maybe 30%) of duplicates.  I would like to know if there is a way to find and delete duplicate data (based on the DateTime record).

Best regards

Andrew Lindsay

Fred Meier

November 28, 2009, 04:41:58 pm #1 Last Edit: November 28, 2009, 11:37:39 pm by TechSupport
Here is one way

   Local lsPriorDateTime as String

   slOpen "Database FileName.Sld"
   slExe "Begin"
   slSel "Select DateTime, RowID from TableA Order by DateTime"
   do while slGetRow
      if slFN("DateTime") = lsPriorDateTime then
         slExe "Delete from TableA where RowID=" & slFN("RowID")
      else
         lsPriorDateTime = slFN("DateTime")
      end if
   loop
   slExe "End"



EDIT: By Paul Squires (I can't seem to post a reply so I am modifying Fred's post)

Maybe something along the lines like described in this post:
http://stackoverflow.com/questions/1173963/delete-duplicate-rows-from-sqlite


DELETE FROM table WHERE rowid NOT IN
    (SELECT MAX(rowid) FROM table GROUP BY date);