Finding duplicate values in a SQL table

2018-12-31 03:06发布

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   asd@asd.com
2    Sam    asd@asd.com
3    Tom    asd@asd.com
4    Bob    bob@asd.com
5    Tom    asd@asd.com

This query will give us John, Sam, Tom, Tom because they all have the same email.

However, what I want is to get duplicates with the same email and name.

That is, I want to get "Tom", "Tom".

The reason I need this: I made a mistake, and allowed to insert duplicate name and email values. Now I need to remove/change the duplicates, so I need to find them first.

25条回答
泪湿衣
2楼-- · 2018-12-31 03:41

How to get duplicate record in table

SELECT COUNT(Code),Code FROM Employees WHERE Status=1 GROUP BY Code HAVING COUNT(Code) > 1

查看更多
登录 后发表回答