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).
Here is one way
Local lsPriorDateTime as String
slOpen "Database FileName.Sld"
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")
lsPriorDateTime = slFN("DateTime")
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:
DELETE FROM table WHERE rowid NOT IN
(SELECT MAX(rowid) FROM table GROUP BY date);