Remove duplicate rows in MySQL

by

The primary key: id, the unique columns:  col_1, col_2, col_3 .

You can use a temporary table, like:

create temporary table temp_table (id int);

insert  temp_table
        (id)
select  id
from    your_table t1
where   exists
        (
        select  *
        from    your_table t2
        where   t2.col_1 = t1.col_1
                and t2.col_2 = t1.col_2
                and t2.col_3 = t1.col_3
                and t2.id > t1.id
        );

delete
from    your_table
where   id in (select id from temp_table);

Or you can add a UNIQUE index on the 3 columns. When you write the

ALTER IGNORE TABLE your_table ADD UNIQUE INDEX idx_name (col_1, col_2, col_3);

This will drop all the duplicate rows. As an added benefit, future INSERTs that are duplicates will error out. And you’d better take a backup before running this statement.

Relative links:

Remove duplicate rows in SQL Server

Remove duplicate rows in Oracle

 

Some MySQL tools you can try: https://www.withdata.com/mysql/

2 thoughts on “Remove duplicate rows in MySQL

  1. Pingback: Remove duplicate rows in Oracle | Withdata Software

  2. Pingback: Remove duplicate rows in SQL Server | Withdata Software