0

I am successfully able to select duplicate queries in phpmyadmin as follow

SELECT *, COUNT(ID)
FROM LINKS
GROUP BY URL
HAVING COUNT( ID ) >1

Now, I need to delete the same entries which are having duplicate entries, what is the query I need to run for the fetched queries. Just before deleting, I want to make sure, that I am running the correct one. Thanks in advance

Markus Malkusch
  • 7,738
  • 2
  • 38
  • 67
sammry
  • 412
  • 4
  • 16

2 Answers2

1

Check out this fiddle. This query works too and keeps the rows with the least IDs...

DELETE L1
FROM LINKS L1
    JOIN LINKS L2 ON L1.URL = L2.URL
WHERE L1.ID > L2.ID
user2989408
  • 3,127
  • 1
  • 17
  • 15
0

Check it:

https://coderwall.com/p/pypbpw?i=4&p=1&q=author%3Amorteza-ipo&t%5B%5D=morteza-ipo

Here is an example of how to remove duplicate rows in MySQL.

DROP FUNCTION IF EXISTS removeDuplicates;
DELIMITER $$
CREATE FUNCTION removeDuplicates(x int)
    RETURNS TINYINT
    BEGIN
        DECLARE x int;
        SET x = x;
        REPEAT

            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
            );

            SET x = x - 1; 
        UNTIL x < 1 END REPEAT;
        RETURN 1;
    END $$
DELIMITER ;

And just call it : 

select removeDuplicates(100);

Good luck

mortymacs
  • 3,456
  • 3
  • 27
  • 53