How to Delete Duplicate Rows in MySQL While Keeping One?

How to remove duplicate rows from MySQL (keep one row)

Deduplication is crucial when maintaining a clean and efficient database. While selecting unique values ​​using a query like SELECT DISTINCT is simple, removing duplicates via the DELETE operation requires a slightly different approach.

Use DELETE to remove duplicates

To remove duplicate rows from a table while keeping a copy, you can use two DELETE query variations:

1. Keep the row with the lowest ID value:

   <code class="language-sql">DELETE n1 FROM names n1, names n2
   WHERE n1.id > n2.id AND n1.name = n2.name</code>

2. Keep the row with the highest ID value:

   <code class="language-sql">DELETE n1 FROM names n1, names n2
   WHERE n1.id < n2.id AND n1.name = n2.name</code>

Note that these queries use inner joins (JOIN) to compare rows based on the name column. They then identify and remove duplicate rows based on the condition that one row has a higher or lower ID value than another row.

Note:

• Be sure to create a test copy of the table before executing these queries, as they may delete all rows if used incorrectly.
• Including the condition AND n1.id != n2.id in the query ensures that rows are not deleted if they have the same name but different ID values.

An alternative to using INSERT:

For larger tables, using DISTINCT with INSERT is a more efficient way to remove duplicates.

<code class="language-sql">INSERT INTO tempTableName(cellId, attributeId, entityRowId, value)
SELECT DISTINCT cellId, attributeId, entityRowId, value
FROM tableName;</code>

This method creates a new table containing only unique values, effectively removing duplicates.

The above is the detailed content of How to Delete Duplicate Rows in MySQL While Keeping One?. For more information, please follow other related articles on the PHP Chinese website!