MySQL - How to remove all duplicate rows?

You have one table class with two columns id and name. Now you want to delete all the rows with the duplicate value for the column name.

If you want to keep the row that shows up first:

DELETE n1 FROM `class` n1,
`class` n2
WHERE n1.id > n2.id
    AND n1.name = n2.name;

Or use MIN:

DELETE FROM `class`
WHERE id NOT IN (
        SELECT
            * FROM (
                SELECT
                    MIN(n.id)
                    FROM `class` n
                GROUP BY
                    n.name) x);

---

If you want to keep the row that shows up last:

DELETE n1 FROM `class` n1,
`class` n2
WHERE n1.id < n2.id
    AND n1.name = n2.name;

Or use MAX:

DELETE FROM `class`
WHERE id NOT IN (
        SELECT
            * FROM (
                SELECT
                    MAX(n.id)
                    FROM `class` n
                GROUP BY
                    n.name) x)

---

Another way is to create a temporary table:

CREATE TABLE temp_class LIKE `class`;

Then add UNIQUE to the column name:

ALTER TABLE temp_class ADD UNIQUE(`name`(20));

INSERT IGNORE from the old table class:

INSERT IGNORE INTO temp_class SELECT * FROM `class` ORDER BY id;

With IGNORE, a row that has duplicate value will be discarded with no error.

After that, you can drop the original table class and rename table temp_class to class.

---

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more.


Download TablePlus free.