Find and delete duplicates from the collection database: Difference between revisions
Jump to navigation
Jump to search
(Created page with "See: https://wiki.strawberrymusicplayer.org/wiki/Accessing_the_database on how to access the database. Exit Strawberry first and backup the file "strawberry.db" in case something goes wrong. You can also use Tools / Console in Strawberry, but it won't show errors, so it's safer to use the sqlite3 command directly. == Find duplicate songs == SELECT s.rowid, s.url FROM songs s INNER JOIN (SELECT url, COUNT(*) FROM songs GROUP BY url HAVING count(*) > 1) d ON s.url...") |
No edit summary |
||
| Line 17: | Line 17: | ||
This must be done, otherwise the table used for the collection search will still have duplicates. | This must be done, otherwise the table used for the collection search will still have duplicates. | ||
DELETE FROM | DELETE FROM songs_fts; | ||
INSERT INTO songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment) SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment FROM songs; | INSERT INTO songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment) SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment FROM songs; | ||
Latest revision as of 23:31, 2 January 2024
See: https://wiki.strawberrymusicplayer.org/wiki/Accessing_the_database on how to access the database.
Exit Strawberry first and backup the file "strawberry.db" in case something goes wrong.
You can also use Tools / Console in Strawberry, but it won't show errors, so it's safer to use the sqlite3 command directly.
Find duplicate songs
SELECT s.rowid, s.url FROM songs s INNER JOIN (SELECT url, COUNT(*) FROM songs GROUP BY url HAVING count(*) > 1) d ON s.url = d.url ORDER BY s.url;
Delete duplicate songs
DELETE FROM songs WHERE rowid NOT IN (SELECT min(rowid) FROM songs GROUP BY url);
Recreate FTS tables
This must be done, otherwise the table used for the collection search will still have duplicates.
DELETE FROM songs_fts;
INSERT INTO songs_fts (ROWID, ftstitle, ftsalbum, ftsartist, ftsalbumartist, ftscomposer, ftsperformer, ftsgrouping, ftsgenre, ftscomment) SELECT ROWID, title, album, artist, albumartist, composer, performer, grouping, genre, comment FROM songs;