Skip to navigation
Delete duplicated mysql entries
25.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/
Reply
Anonymous
Information Epoch 1713578336
Design for visibility.
Home
Notebook
Contact us