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

Published on 11-Jan-2019 12:21:30

• Related Questions & Answers
• How to check multiple columns for a single value in MySQL?
• How to create conditions in a MySQL table with multiple columns?
• Count value for multiple columns in MySQL?
• MySQL Select Statement DISTINCT for Multiple Columns?
• MySQL check for crashed table?
• How to search multiple columns in MySQL?
• Set multiple values for custom columns in MySQL?
• Count duplicates records in MySQL table?
• How we have multiple stored GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
• How to check for duplicates in data.table object in R?
• How can we have multiple virtuals GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
• Searching multiple columns for a row match in MySQL
• MySQL multiple COUNT with multiple columns?
• How can we add multiple columns, with single command, to an existing MySQL table?
• How to order MySQL rows by multiple columns?