CODEWITHSUNDEEP
mysqlsql
George
George

Reputation: 1114

How to delete the more than one duplicate row in mysql

id   number
1       20
2       20
3       30
4       18

Hello guys, I have a table called numbers,this table contains thousands of rows--sample above--The table has so many duplicates. I want a way to simply delete those duplicates using mysql query. The query should also delete the original number and the duplicate itself. For instance the id with 1 and 2 value should be deleted since they are duplicate. Thanks for helping

Upvotes: 0

Views: 96

Answers (2)

radar
radar

Reputation: 13425

We can get duplicates with group by and having clause

Delete a.* from TableA a
Where Number in ( 
Select number from (
select number from tableA 
group by number
having count(*) > 1)
)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269513

In MySQL, you can do this with a join:

delete n
    from numbers n join
         (select number
          from numbers
          group by number
          having count(*) > 1
         ) nn
         on n.number = nn.number;

Here is an example on SQL Fiddle.

Upvotes: 2

Related Questions