how can I delete duplicates in SQLite?

sqlite sql-delete
Walter Schrabmair picture Walter Schrabmair · Sep 17, 2014 · Viewed 17.5k times · Source

I have a SQLite DB where the statement: 

SELECT messdatum, count(*) as anzahl 
from lipo 
GROUP BY Messdatum 
ORDER BY anzahl desc;

results in some lines, which indicates that I have some duplicates with the same Messdatum. How can I delete the duplicates only form my sqlite db? (it should delete anzahl-1 records where the messdatum is the same?) Has anyone an advice?

PS: I found this link How to remove duplicate from Microsoft but have problems with sqlite dialect. I got some errors due to the sqlite syntax. So f.e. I could do:

 INSERT into holdkey SELECT messdatum, count(*) as anzahl from lipo group by messdatum having count(*) > 1;

 INSERT into holddups SELECT DISTINCT lipo.* from lipo, holdkey where lipo.Messdatum = holdkey.messdatum ;

 DELETE lipo from lipo, holdkey where lipo.messdatum = holdkey.messdatum;

here is an error at the delete command. How can I do that? I tried to update the holdkey.anzahl to an additional col in lipo with 

UPDATE lipo,holdkey set lipo.duplettenzahl = holdkey.anzahl WHERE lipo.messdatum = holdkey.messdatum ;

but this is also not possible. If I would have the anzahl as dublettenzahl in lipo I could delete all records from lipo where dublettenzahl > 0. Please help! Thanks

Answer

Mike Woodhouse picture Mike Woodhouse · Sep 17, 2014

SQLite has a special column, ROWID created on every table by default (you can switch it off using the WITHOUT ROWID modifier, but be very sure before doing so).

This means that we can identify specific rows within sets of duplicates, for example, finding the first entry for a value:

SELECT messdatum, MIN(ROWID) FROM lipo

So one way to remove duplicates might be this:

DELETE FROM lipo
WHERE rowid NOT IN (
  SELECT MIN(rowid) 
  FROM lipo 
  GROUP BY messdatum
)

Related questions

How to Delete All Items From SQLite in Android

I would like to make an app where the user clicks a button, and the SQLite database is cleared. Here's what I've tried so far: db.delete(TABLE_NAME, null, null); What am I doing wrong?

Read More
Remove all table rows from SQLite database table

I want to remove all rows that i entered from my SQLite database table. The table name is tbltask. I tried to drop the table and delete * from table, but those are giving me runtime errors. I want to trigger …

Read More
Delete SQLite Row with where clause with multiple clauses

I've been searching around and not managed to find a solution or working example so here goes. I've set up an SQLite database which has five columns with different fields which effectively builds a list for the user. There will …

Read More