MySQL - How to delete duplicate rows (but leave 1 row) given 2 columns?

Question

This table only has 2 columns. There are rows considered "duplicate" when both of the row's columns match.

That is:

col1 col2
X    X
X    X
X    Y  -- this is not a duplicate

I want to delete the duplicates but leave at least one of them. It doesn't matter which because they are the same thing.

I've used variations of IN and JOIN, but I can't seem to get the DELETE outer query to only delete one of each row with duplicate columns.

Possible duplicate of [Remove duplicate rows in MySQL](http://stackoverflow.com/questions/3311903/remove-duplicate-rows-in-mysql) — Jens, Feb 05 '16 at 13:00
There's no PRIMARY KEY. And version: 5.5.35-0ubuntu0.12.04.2 — CMCDragonkai, Feb 05 '16 at 13:03
Consider creating a new table (WITH A PRIMARY KEY) using only distinct values from the old table. Then drop the old table. — Strawberry, Feb 05 '16 at 13:04
Duplicate question about duplicates: http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table — Dennis, Feb 05 '16 at 13:06
I'd try the `ALTER IGNORE TABLE table1 ADD UNIQUE INDEX idx_name (col1,col2)`; option in the question that @Jens linked, you can drop the indx afterwards but you probably want to keep it. — Arth, Feb 05 '16 at 13:06
It does seem the more simplest manner of achieving this is through indexing pairs like how @Arth suggested. — CMCDragonkai, Feb 05 '16 at 13:09
@Strawberry it looks like there may be some settings that need to be tweaked first, see the answer in the duped question. — Arth, Feb 05 '16 at 13:38

Rahul Tripathi · Answer 1 · 2016-02-05T13:17:47.913

1

Try this:

DELETE  a
FROM    mytable a
        LEFT JOIN
        (
            SELECT MIN(ID) ID, col1, col2
            FROM    mytable
            GROUP   BY col1, col2
        ) b ON  a.ID = b.ID AND
                a.col1 = b.col1 AND
                a.col2 = b.col2
WHERE   b.ID IS NULL

DEMO

Assuming ID as primary key column

EDIT:

However if you dont have the ID column then you can try like this:

ALTER IGNORE TABLE mytable
  ADD UNIQUE INDEX all_columns_uq
    (col1, col2) ;

DEMO

edited Feb 05 '16 at 13:17

answered Feb 05 '16 at 13:03

Rahul Tripathi

168,305
31
280
331

1

"This table only has 2 columns." – Arth Feb 05 '16 at 13:04

Arth · Answer 2 · 2016-02-05T13:41:30.743

1

ALTER IGNORE TABLE table1 ADD UNIQUE INDEX idx_name (col1,col2);

OR

CREATE TABLE table1_temp AS
SELECT * FROM table1 GROUP BY col1, col2;

TRUNCATE TABLE table1;
INSERT INTO table1 SELECT * FROM table1_temp;

DROP TABLE table1_temp;

You may lose data with the second method on a live table though, also any other tables referencing it may not be too happy!

I'd suggest adding the unique index too, just to future proof yourself.

edited Feb 05 '16 at 13:41

answered Feb 05 '16 at 13:16

Arth

12,789
5
37
69

@strawberry please don't edit my answer to remove large chunks of text and make your own preferred/superficial changes.. add a comment if you want to suggest changes. – Arth Feb 05 '16 at 13:42

MySQL - How to delete duplicate rows (but leave 1 row) given 2 columns?

2 Answers2