How Can I Delete Duplicates In Sqlite?

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 h

Solution 1:

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:

DELETEFROM lipo
WHERE rowid NOTIN (
  SELECTMIN(rowid) 
  FROM lipo 
  GROUPBY messdatum
)

Solution 2:

I got the solution:

INSERTinto holdkey SELECT messdatum, count(*) as anzahl,NameISO from lipo groupby messdatum havingcount(*) >1;
 INSERTinto holddups SELECTDISTINCT lipo.*,1from lipo, holdkey where lipo.Messdatum = holdkey.messdatum groupby messdatum;
 INSERTinto lipo_mit_dz  SELECT*, count(*) as DublettenZahl from lipo groupby messdatum ORDERBY Dublettenzahl desc ;
 DELETEfrom lipo_mit_dz where Dublettenzahl >1;
 INSERTinto lipo_mit_dz SELECT*from holddups ;