I received a database with a few million records in it, but apperently there might be duplicate records in them.
A user enters data into the database and a primary key is generated, however if the user enters the same data again, a new primary key will be generated for that data, even though the data has already been entered before. There are no checks on this.
I need to go looking for these duplicates, but I do not really know where to start. I first thought concatenating all cells except the primary key in a subquery and then count these rows and see which ones have a count higher than 1.
cfr.
pkey recipe fkey comment
1 toast 3 tasty
2 curry 2 spicy
3 curry 2 spicy
4 bread 1 crumbly
5 orios 2 cookies
Here the curry entries are identical and I'd have to delete 1 of those.
However I read concatenating is unpredictable in mysql and it just feels a bit wrong to me as well.
Any hints ?