Finding duplicate values in a SQL table
- by Alex
It's easy to find duplicates with one field
SELECT name,
COUNT(email)
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
So if we have a table
ID NAME EMAIL
1 John [email protected]
2 Sam [email protected]
3 Tom [email protected]
4 Bob [email protected]
5 Tom [email protected]
This query will give us John, Sam, Tom, Tom because they all have the same e-mails.
But what I want, is to get duplicates with the same e-mails and names.
I want to get Tom, Tom.
I made a mistake, and allowed to insert duplicate name and e-mail values. Now I need to remove/change the duplicates. But I need to find them first.