CODEWITHSUNDEEP
mysql
CMCDragonkai
CMCDragonkai

Reputation: 6372

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

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.

Upvotes: 1

Views: 158

Answers (3)

Alireza Shamsian
Alireza Shamsian

Reputation: 1

Here is a way by using a CTE and row_Number function

    ; WITH DuplicateRecords AS (
                                SELECT ROW_NUMBER() OVER(PARTITION BY col_1, col_2 ORDER BY col_1 ) AS RW,
                                col_1, 
                                col_2
                                FROM [TABLE]

)
DELETE T 
FROM [TABLE] AS T
    INNER JOIN DuplicateRecords AS D ON D.col_1 = T.col_1 AND D.col_2 = T.col_2 AND D.RW > 1

Upvotes: 0

Arth
Arth

Reputation: 13110

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.

Upvotes: 1

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

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

Upvotes: 1

Related Questions