Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How do I delete duplicate rows and keep the first row?

I made a mistake and I have unwanted duplicates.

I have a table with 4 key fields. A1, k1, k2, k3.

A1 is auto increment and the primary key.

the combination of k1, k2 and k3 is supposed to be unique and I have to delete the duplicate rows before I create a unique index. Some rows have one duplicate, some have many.

SELECT CONCAT(k1, k2, k) AS dup_value
  FROM myviews
 GROUP BY dup_value
HAVING (COUNT(dup_value) > 1)

shows me duplicates values that I need to deal with. But now I don't know how to keep one and delete the rest of each duplicate set.

like image 670
sdfor Avatar asked May 23 '11 21:05

sdfor


People also ask

How can I retain the row position when removing duplicate values?

Remove duplicates but keep rest of row values with FilterWith a formula and the Filter function, you can quickly remove duplicates but keep rest. 5. Click Data > Filter to disable Filter, and remove the formulas as you need. You can see all duplicates have been removed and the rest of values are kept in the row.

How do I eliminate duplicate rows?

Use DataFrame. drop_duplicates() to Drop Duplicate and Keep First Rows. You can use DataFrame. drop_duplicates() without any arguments to drop rows with the same values on all columns.


2 Answers

Backup your data, then...

MySQL supports JOINs in DELETE statements. If you want to keep the first of the duplicates:

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MIN(t.a1) AS min_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.min_a1 != a.a1

If you want to keep the last of the duplicates:

DELETE a
  FROM MYVIEWS a
  JOIN (SELECT MAX(t.a1) AS max_a1, t.k1, t.k2, t.k3
          FROM MYVIEWS t
      GROUP BY t.k1, t.k2, t.k3
        HAVING COUNT(*) > 1) b ON b.k1 = a.k1
                              AND b.k2 = a.k2
                              AND b.k3 = a.k3
                              AND b.max_a1 != a.a1
like image 70
OMG Ponies Avatar answered Oct 06 '22 10:10

OMG Ponies


You can create a new table with the same structure but empty, then create the unique key on it, then do a INSERT IGNORE / SELECT * FROM the original table into the new table, then delete the original table.

INSERT IGNORE will automatically ignore any primary or unique key issues and just skip the duplicates.

like image 2
spanky Avatar answered Oct 06 '22 12:10

spanky