How to Delete Duplicates in a MySQL Table

When working with a database, it can happen that a table contains duplicates, meaning rows with the same values in one or more columns.

Deleting duplicates is crucial for maintaining data integrity and optimizing queries. To do this, you can use the DISTINCT clause to retrieve only unique values or the GROUP BY clause to group rows with the same values. However, if you wish to physically remove duplicates from the table, you will need to use a delete query.

MySQL Query to Delete Duplicates. Here is an example of a MySQL query to delete duplicates from a table named nomi based on the nome column:

DELETE FROM nomi
WHERE id NOT IN (
  SELECT MIN(id)
  FROM nomi
  GROUP BY nome
);

This query deletes all rows from the nomi table where the ID does not match the minimum ID within each group of rows with the same value in the nome column. In other words, it keeps only one row for each unique value in the nome column and deletes all others.

Ensure you adapt this query to your table name and the column on which you want to remove duplicates. Additionally, it is always advisable to back up your data before executing a delete query to prevent accidental information loss.

Pubblicato in MySQL