How to delete duplicate records in MySQL?

Question

I'm working on a POC where I store videos with links and display them on my website. Unfortunately, I wasn't paying attention and my spider which is crawling for videos inserted some videos twice in my database.

I tried using INNER JOIN but it was causing a disconnect and deleting my entire database (11k entries on my test database and 45k on my website).

DELETE V1 FROM videos AS V1 INNER JOIN videos AS V2 WHERE V1.title = V2.title;

score 0 · Answer 1 · answered Jun 15 '21 at 21:32

0

The issue here is you match your own record; table V1 and V2 have the very same data, so it will always match the title with itself.

You need to exclude the first record ID which exists in the table.

DELETE V1 FROM videos AS V1 
    INNER JOIN videos AS V2 
    WHERE V1.title = V2.title and V1.id > V2.id;

This should always leave intact the first record.

answered Jun 15 '21 at 21:32

Guillaume F.

5,905
2
31
59

There is a similar answer here: https://stackoverflow.com/a/58687372/5444618 – Guillaume F. Jun 16 '21 at 11:33

score 0 · Answer 2 · answered Jun 21 '21 at 12:05

You can do this with a JOIN statement. For Example: DELETE t1 FROM contacts t1 INNER JOIN contacts t2 WHERE t1.id < t2.id AND t1.email = t2.email;

You can refer to this link below for more information:

https://www.mysqltutorial.org/mysql-delete-duplicate-rows/

How to delete duplicate records in MySQL?

2 Answers2