How to Find Duplicate Records in MySQL

Last updated on January 29th, 2021 at 06:13 am

Sometimes you may need to find duplicate records in MySQL. Here’s how to get duplicate records in a table. You can use it to get rows with duplicate values in MySQL

How to Find Duplicate Values in MySQL

Here are the steps to get duplicate records in MySQL. Let’s say you have the following MySQL table with duplicate records.

mysql> create table dup_orders(id int, amount int);

mysql> insert into dup_orders(id,amount) values(1, 100),(1,250),(2,350),(2,350);

mysql> select * from dup_orders;
+------+--------+
| id   | amount |
+------+--------+
|    1 |    100 |
|    1 |    250 |
|    2 |    350 |
|    2 |    350 |
+------+--------+

Bonus Read : MySQL Add Unique Constraint

Find Duplicate Row values in One Column

Here’s the SQL query to find duplicate values for one column

SELECT 
    col, 
    COUNT(col)
FROM
    table_name
GROUP BY col
HAVING COUNT(col) > 1;

In the above query, we do a GROUP BY for the column for which we want to check duplicates. We also use a COUNT() and HAVING clause to get the row counts for each group.

Bonus Read : MySQL Rename Column

Let’s apply the above query for our dup_orders table, to get duplicate values for id column

mysql> select id, amount
       from dup_orders
       group by id
       having count(id)>1;
+------+--------+
| id   | amount |
+------+--------+
|    1 |    100 |
|    2 |    350 |
+------+--------+

Find Duplicate Row values in Multiple Columns

Here’s the SQL query to find duplicate values for multiple columns

SELECT 
    col1, col2,..., COUNT(*)
FROM
    table_name
GROUP BY 
    col1, 
    col2,
    ...
HAVING 
       (COUNT(col1) > 1) AND 
       (COUNT(col2) > 1) AND
       ...

In the above query, we do a GROUP BY of all the columns (col1, col2) for whom we want to find duplicates. We also use a COUNT() and HAVING clause to get the row counts for each group.

Bonus Read : How to Duplicate Table in MySQL

Let’s apply the above query for our dup_orders table, to get duplicate values for id and amount column

mysql> select id, amount,count(*)
       from dup_orders
       group by id,amount
       having count(id)>1
       and count(amount)>1;
+------+--------+----------+
| id   | amount | count(*) |
+------+--------+----------+
|    2 |    350 |        2 |
+------+--------+----------+

Hopefully, now you can easily get duplicate records in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.