I recently migrated users from an 8.6 to 11.5 cluster and, between fixing all of the table errors caused by db changes between versions, we ended up with 45,000 subscribed services for 8,000 endpoints. Exporting and re-importing was going to be a nightmare so I exported some tables to an SQLite server and set about playing with SQL. I now have a working statement which deleted 37,000 entries out of the database and left the unique entries.
run sql delete from telecastersubscribedservice where pkid not in (select max(pkid) from telecastersubscribedservice group by fkdevice, fktelecasterservice having max(pkid) is not null)
Since this is a delete on the raw database table, you're on your own if it breaks. Take a backup (run it up in the lab and test on the backup) before using on a live system. The SQL works on my external db and the query runs correctly as an SQL select on the live system. Deleting a single entry from that table using SQL has the desired effect and I've found no issues, but I've yet to run it in anger. I've posted it here as I've hit this issue in the past and I can't see any better articles so I hope that this helps if you need it.
Good luck,
Joe
