How to remove duplicate rows in MySQL?

Question

I tried to remove duplicate rows from a table TT

here is my query

delete t1 
  from TT t1
     , TT t2 
 where t1.id < t2.id 
   and t1.url = t2.url

Here id is the primary key and url has the unique key in the table TT. You must be wondering why there are duplicate rows with unique index?

Actually it did happen and I don't know why but right now I want to remove the duplicate rows first. I am able to run the query in phpmyadmin but no duplicate rows are deleted at all(There is duplicate rows in the Table TT).

What could be the reason? Thanks!

Actually I also tried to use join in the query even before I used the query I already posted. Here is the query, [code] delete t1 from TT t1 inner join TT t2 where t1.id < t2.id and t1.url = t2.url [/code] But it still does not the job as I expected. No duplicate rows have been removed. I guess does it have anything to do with privilege in phpmyadmin? I did login in as a root user and I checked I have the all the rights. Thanks. — dullboy
– dullboy, Commented Jul 15, 2020 at 21:05
If url has the unique key in the table TT then there are no duplicates. Check again for blank not visible chars in the column. — forpas
– forpas, Commented Jul 15, 2020 at 21:32
Before I tried to remove the duplicate rows, I called the following query to check the duplicate rows, [code] select url from TT group by url having count(id) > 1 [/code] It did return duplicate url. Thanks. — dullboy
– dullboy, Commented Jul 15, 2020 at 21:41
If you really have duplicate unique indexes then that seems like a bigger problem than how to remove them. You'll never be able to trust your data. — Honeyboy Wilson
– Honeyboy Wilson, Commented Jul 15, 2020 at 21:44
As a side note. If I move my mouse over the query, phpmyadmin did show there is an error "Unexpected token.(near t1)". But I am still able to run the query but it just doesn't remove the duplicate rows. So I am just wondering if the innocent looking query might not be syntax correct especially in phpmyadmin? — dullboy
– dullboy, Commented Jul 15, 2020 at 21:49

Eric · Accepted Answer · 2020-07-15 21:24:58Z

2

You can use ROW_NUMBER() to remove duplicate

;WITH cte AS (
    SELECT *
        , ROW_NUMBER OVER(PARTITION BY url ORDER BY url) AS rn
    FROM TT
)
DELETE FROM cte 
WHERE rn > 1

answered Jul 15, 2020 at 21:24

Eric

3,2701 gold badge22 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Bill Karwin Over a year ago

Note that window functions requires MySQL 8.0.2, and not everyone has upgraded yet.

Collectives™ on Stack Overflow

How to remove duplicate rows in MySQL?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related