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 ho开发者_如何学JAVAw to keep one and delete the rest of each duplicate set.

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

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.

Someting like this?

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

You need a separator in your concat function, because otherwise "a", "b", and "cd" is the same as "abcd", "", "".