How to remove duplicate rows from MySQL table

Question

I have a MySQL table, for example:

ID, Col1, Col2, Col3, Col4, etc.

ID is the primary key and has been working since the table was created.

What I want to do is delete everything except one record, where all other columns are identical.

Answer 1

DELETE DupRows.* FROM MyTable AS DupRows INNER JOIN ( SELECT MIN(ID) AS minId, col1, col2 FROM MyTable GROUP BY col1, col2 HAVING COUNT(*) > 1 ) AS SaveRows ON SaveRows.col1 = DupRows.col1 AND SaveRows.col2 = DupRows.col2 AND SaveRows.minId <> DupRows.ID; 

Of course, you must extend col1, col2 in all three places to all columns.

Edit: I just pulled this from a script, I continue and checked, it runs in MySQL.

Answer 2

• RENAME TABLE [table w/ duplicates] TO [temporary table name]

• Create an identical table with the name of the original table containing duplicates.

• INSERT INTO [new table] SELECT DISTINCT * FROM [old table with duplicates]

• Delete temporary tables.

Answer 3

No nested selections or temporary tables.

 DELETE t1 FROM table_name t1, table_name t2 WHERE (t1.Col1 = t2.Col1 OR t1.Col1 IS NULL AND t2.Col1 IS NULL) AND (t1.Col2 = t2.Col2 OR t1.Col2 IS NULL AND t2.Col2 IS NULL) AND (t1.Col3 = t2.Col3 OR t1.Col3 IS NULL AND t2.Col3 IS NULL) AND (t1.Col4 = t2.Col4 OR t1.Col4 IS NULL AND t2.Col4 IS NULL) ... AND t1.ID < t2.ID; 

Answer 4

I would do it as follows in MSSQL, but I think it should work with minor changes in MySQL. Not executable, but should show the way.

 CREATE TEMPORARY TABLE #Table (Col1, Col2, Col3); INSERT INTO #Table (Col1, Col2, Col3) SELECT DISTINCT Col1, Col2, Col3 FROM Table; DELETE FROM Table; INSERT INTO Table (Col1, Col2, Col3) SELECT Col1, Col2, Col3 FROM #Table; DROP TABLE #Table; 

Answer 5

you can also do it

 Create table new_table{id, col1,col2,col3} insert into new_table values(select distinct * from old_table) drop table old_table 

Answer 6

you can delete all but one row using some function like Min (depends on db). Example:

 delete from Table_Name where Id not in ( select min(Id) from Table_Name group by ID, Col1, Col2, Col3, Col4); 

Answer 7

You can try this with a connection: Thus:

 DELETE e1 FROM emp_tbl AS e1 JOIN emp_tbl AS e2 WHERE e1.Col1=e2.Col1 AND e1.Col2=e2.Col2 AND e1.Col3=e2.Col3 AND e1.Col4=e2.Col4 AND e1.id < e2.id; 

Answer 8

You can run an alternative query and achieve this:

 ALTER IGNORE TABLE tbl_1 ADD UNIQUE INDEX unq_idx(col1, col2, col3); 

I cannot guarantee that it will keep the first record among duplicates, but MySQL usually does.