Just some random snippets of SQL I always have to search to find (or spend an inordinate amount of time thinking about). Will be updated as I find them…
Get number of duplicate records:
SELECT COUNT(*) FROM table WHERE id NOT IN (SELECT MAX(id) FROM table GROUP BY col1, col2, col3);
Delete duplicate records:
DELETE FROM table WHERE id NOT IN (SELECT MAX(id) FROM table GROUP BY col1, col2, col3);
(Where col1, col2, col3 are the columns you’re looking for duplicates on – from Stack Overflow, tested with SQLite on ~10000 records, took <2s)