Re: Deleting duplicating rows from the table except one

[Chris Stubben]

Hi,

Try this.



drop table tmp;
create table tmp(id int);

insert into tmp values (1), (2), (3), (4), (2), (2), (3);
select * from tmp;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 2 |
| 2 |
| 3 |
+------+

EASY

Add a primary or unique key and duplicates will be removed.


alter ignore table tmp add unique key (id);

Records: 7 Duplicates: 3 Warnings: 0

select * from tmp;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+


OR if your table has a primary key (like the rowid pseudocolumn in oracle) you can use a self-join



alter table tmp add column rowid int not null auto_increment primary key first;
+-------+------+
| rowid | id |
+-------+------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
| 4 | 4 |
| 5 | 2 |
| 6 | 2 |
| 7 | 3 |
+-------+------+


select DISTINCT t1.rowid as dups from tmp t1, tmp t2 where t1.id=t2.id and t1.rowid>t2.rowid
+------+
| dups |
+------+
| 5 |
| 6 |
| 7 |
+------+


Right now in mysql you can't use the same table in the delete clause and subquery from clause.

delete from tmp where rowid in (select DISTINCT t1.rowid from tmp t1, tmp t2 where t1.id=t2.id and t1.rowid>t2.rowid);

So create a temporary table...

create temporary table dups as select DISTINCT t1.rowid from tmp t1, tmp t2 where t1.id=t2.id and t1.rowid>t2.rowid;
+-------+
| rowid |
+-------+
| 5 |
| 6 |
| 7 |
+-------+

and then delete

delete from tmp where rowid in (select rowid from dups);


select id from tmp;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+



Note this query with delete ALL rows with duplicates.

delete from tmp
where id in (select id from tmp group by id having count(*)>1) ;

In mysql, you can use a delete with a limit one less than the number of rows.

delete from tmp where id=2 limit 2;
delete from tmp where id=3 limit 1;

Chris