Last Updated: February 25, 2016
·
1.866K
· Mortezaipo

Removing duplicates rows in MySQL

In a project I had a problem that the system made duplicate rows in my table.

My table fields are : id,from,to,msg,created => example: 1,1000,2000,'hi this is test' ,'2013-12-12'

So for getting duplicates rows :

SELECT id, COUNT( * ) AS c,  `to` ,  `msg` 
FROM  `my-queue` 
GROUP BY  `to` ,  `msg` 
HAVING c >1

Now for removing duplicate rows :

DELETE FROM `my-queue` WHERE id IN 
(
    SELECT id FROM 
    (
        SELECT id, COUNT( * ) AS c,  `to` ,  `msg` 
        FROM  `my-queue` 
        GROUP BY  `to` ,  `msg` 
        HAVING c >1
    ) as tem1
)

That was simple , but useful.

Be success