How to Delete Duplicate Rows in MySQL

Hi All,

In this section we are going to delete duplicate rows in a table in MySQL database.

We have following table with name “testtbl”.

+----+---------+-----------+
| id | name    | mobile_no |
+----+---------+-----------+
|  1 | John    |     12345 |
|  2 | Maya    |     54321 |
|  3 | Roy     |     98765 |
|  4 | Tony    |     12345 |
|  5 | Harry   |     12345 |
|  6 | Sheldon |     12345 |
|  7 | xyz     |     54321 |
+----+---------+-----------+

We have query to delete duplicate rows from table.

………………………………………………………………………………………………………………..

DELETE FROM `testtbl`

WHERE `mobile_no` IN(

    SELECT  `mobile_no`

        FROM (

                SELECT `mobile_no`

                FROM `testtbl`

                GROUP BY `mobile_no`

                HAVING COUNT(`mobile_no`)>1)temp

                );

Output:-

6 row(s) affected

Execution Time : 0.076 sec
Transfer Time  : 0 sec
Total Time     : 0.076 sec

<Syntax>

DELETE FROM <Table Name>

WHERE <Column name1> IN(

    SELECT  <Column name1>

        FROM (

                SELECT <Column name1>

                FROM <Table Name>

                GROUP BY <Column name1>

                HAVING COUNT({<Column name1>}>1) <Temporary Table Name>

                );


 

Let’s Understand this query that how it works.

The query is simple up to this part.

DELETE FROM `testtbl`

WHERE `mobile_no` IN(………

 

DELETE is simple delete query.

Now “IN”clause, IN clause is just like OR operator.

Rather use OR clause, we can use IN clause and pass condition in round braces or can pass comma separated string for condition, Click here to know complete working of IN Clause.

Next is:-

 

SELECT  `mobile_no`

        FROM (

                SELECT `mobile_no`

                FROM `testtbl`

                GROUP BY `mobile_no`

                HAVING COUNT(`mobile_no`)>1)temp

                );

Let’s break this query.

First focus on,

SELECT `mobile_no`

                FROM `testtbl`

                GROUP BY `mobile_no`

                HAVING COUNT(`mobile_no`)>1)temp

 

Output:-

+-----------+
| mobile_no |
+-----------+
|     12345 |

 

So this query returns result in “temp” named temporary table.

This SELECT query select “mobile_no” column from “testtbl” table which is grouped using GROUP BY clause and should have count greater than 1. This result will stored in “temp” table.

Now have a look on this part of Query.

 

SELECT  `mobile_no`

FROM (…….<result from temp table>

 

This SELECT query just select “mobile_no” and pass the records to DELETE Query.

The basic logic is.

Following query first selects the duplicate rows and creates a temporary table temp and the deletes the duplicate rows.

This Query will delete all the duplicate rows.

I hope you understand the basic concept behind this query and how this works.

No Comments

Post a Comment