MySQL Get Duplicate Records

Sometimes you may need to find duplicate records in MySQL. Here’s how to get duplicate records in a table.

 

How to Find Duplicate Records 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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!