[SOLVED] duplicate database entries

Issue

I've accidentally managed to add duplicate entries into my database. The database contains a list of telephone numbers and they are routed via the information contained in the value field. The id field is unique per entry, and the UUID and username fields should be identical but shouldn't exist in the table more than once.

Data has been blanked in the screenshot for data protection.

The following command allowed me to identify I had duplicate entries which can be seen in the screenshot above.

select * uuid, count(*) from usr_preferences group by uuid having count(*) > 1;

I'm after some help on how I could delete entries where the UUID count is more than one but one entry must remain. deleting the duplicate UUID with the highest id number would be preferred.

Is there a way to display the results before deleting them?

MySQL version - mysql Ver 14.14 Distrib 5.7.38-41, for Linux (x86_64) using 6.2

Thanks

Solution

Could you give the following bit of code a go? Please make sure you have the database backed up before running this.

DELETE b FROM `test` a, `test` b where b.uuid = a.uuid and b.id > a.id;

I've expanded on your text data to make sure it will remove both duplicates and triplicates leaving the lowest ID. You can find my testing at this DB Fiddle.

https://www.db-fiddle.com/f/sUr6V6UP9tZ1Ya8eESid33/0

Hope this sorts you issue.

Answered By - Alan Tiller

Answer Checked By - Katrina (WPSolving Volunteer)