Delete duplicates entries in SQLite

I have one table with duplicate entries. Other than the primary_key all the entries are the same.

The entries even stores a unique开发者_如何学Python id that will be the same if the entries are duplicates (secondID).

Even if it does not seem hard I'm having trouble coming up with a rawQuery in Android that delete duplicate entries based on the second id.

dataBase.rawQuery("DELETE FROM " + table + " WHERE " + secondID + " EQUALS " + secondID + ");", null);

I'm probably quite wrong about this query but seems like I've tried some really complicated queries and some really simple like the one above with no luck.

Any help would be appreciated.

Or something like this:

DELETE FROM table WHERE primary_key NOT IN (SELECT MIN(primary_key) FROM table GROUP BY secondID)

Which should pick the primary key of the first instance of every distinct secondID, and delete everything that isn't in that list.

try this..

DELETE FROM table a1 USING table b1 WHERE a1.anyfield=b1.anyfiled AND a1.id<b1.id;

a1 and b1 is same table

This will work:

dataBase.execSQL("DELETE FROM " + table + " WHERE " + secondID + "=" + secondID);

Try this:

dataBase.rawQuery("DELETE FROM " + table + " WHERE " + secondID + " = " + secondID + ");", null);