How to check for duplicates in MySQL table over multiple columns?

To check for duplicates in MySQL, you can use group by having clause. The syntax is as follows.

select yourColumnName1,yourColumnName2,......N,count(*) as anyVariableName from
yourTableName
group by yourColumnName1,yourColumnName2
having count(*) > 1;

To understand the above syntax, let us create a table. The query to create a table is as follows.

mysql> create table DuplicateDemo
-> (
-> StudentId int not null,
-> StudentFirstName varchar(100),
-> StudentLastName varchar(100),
-> Primary Key(StudentId)
-> );
Query OK, 0 rows affected (0.50 sec)

Insert some records in the table using insert command. The query is as follows.

mysql> insert into DuplicateDemo values(1,'John','Smith');
Query OK, 1 row affected (0.13 sec)

mysql> insert into DuplicateDemo values(2,'Mike','Jones');
Query OK, 1 row affected (0.28 sec)

mysql> insert into DuplicateDemo values(3,'David','Smith');
Query OK, 1 row affected (0.15 sec)

mysql> insert into DuplicateDemo values(4,'Carol','Taylor');
Query OK, 1 row affected (0.20 sec)

mysql> insert into DuplicateDemo values(5,'David','Smith');
Query OK, 1 row affected (0.11 sec)

mysql> insert into DuplicateDemo values(6,'John','Smith');
Query OK, 1 row affected (0.16 sec)

mysql> insert into DuplicateDemo values(7,'John','Taylor');
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement.

The query is as follows -

mysql> select *from DuplicateDemo;

The following is the output.

+-----------+------------------+-----------------+
| StudentId | StudentFirstName | StudentLastName |
+-----------+------------------+-----------------+
| 1         | John             | Smith           |
| 2         | Mike             | Jones           |
| 3         | David            | Smith           |
| 4         | Carol            | Taylor          |
| 5         | David            | Smith           |
| 6         | John             | Smith           |
| 7         | John             | Taylor          |
+-----------+------------------+-----------------+
7 rows in set (0.00 sec)

Here is the query to check the duplicates from the table.

mysql> select StudentFirstName,StudentLastName,count(*) as Total from DuplicateDemo
-> group by StudentFirstName,StudentLastName
-> having count(*) > 1;

The following is the output.

+------------------+-----------------+-------+
| StudentFirstName | StudentLastName | Total |
+------------------+-----------------+-------+
| John             | Smith           | 2     |
| David            | Smith           | 2     |
+------------------+-----------------+-------+
2 rows in set (0.00 sec)

George John

e

Updated on: 30-Jul-2019

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started