- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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)
- Related Articles
- How to check multiple columns for a single value in MySQL?
- How to create conditions in a MySQL table with multiple columns?
- How we have multiple stored GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
- How to search multiple columns in MySQL?
- How can we have multiple virtuals GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
- Count value for multiple columns in MySQL?\n
- Set multiple values for custom columns in MySQL?
- MySQL Select Statement DISTINCT for Multiple Columns?
- Count duplicates records in MySQL table?
- How can we add multiple columns, with single command, to an existing MySQL table?
- How to check for duplicates in data.table object in R?
- Searching multiple columns for a row match in MySQL
- MySQL check for crashed table?
- How to calculate value from multiple columns in MySQL?
- How to list temporary table columns in MySQL?

Advertisements