Delete duplicated mysql entries25.07.21

DELETE t1 
FROM isin t1
INNER JOIN isin t2 
WHERE t1.id < t2.id 
AND t1.isin = t2.isin
AND t1.wkn = t2.wkn;

A) Delete duplicate rows using DELETE JOIN statement

MySQL provides you with the DELETE JOIN statement that allows you to remove duplicate rows quickly.

The following statement deletes duplicate rows and keeps the highest id:

DELETE t1 FROM contacts t1
INNER JOIN contacts t2 
WHERE 
    t1.id < t2.id AND 
    t1.email = t2.email;
Code language: SQL (Structured Query Language) (sql)

This query references the contacts table twice, therefore, it uses the table alias t1 and t2.

The output is:

Query OK, 4 rows affected (0.10 sec)
Code language: CSS (css)

It indicated that four rows have been deleted. You can execute the query that find duplicate emails again to verify the delete:

SELECT 
    email, 
    COUNT(email)
FROM
    contacts
GROUP BY 
    email
HAVING 
    COUNT(email) > 1;
Code language: SQL (Structured Query Language) (sql)

The query returns an empty set, which means that the duplicate rows have been deleted.

Let’s verify data from the contacts table:

SELECT * FROM contacts;
Code language: SQL (Structured Query Language) (sql)

MySQL delete duplicate rows - DELETE JOIN keeps Highest ID

The rows with id 2, 4, 7, and 9 have been deleted.

In case you want to delete duplicate rows and keep the lowest id, you can use the following statement:

DELETE c1 FROM contacts c1
INNER JOIN contacts c2 
WHERE
    c1.id > c2.id AND 
    c1.email = c2.email;

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

Anonymous

Delete duplicated mysql entries25.07.21

Information Epoch 1664470550