How to Efficiently Delete Duplicate Rows in MySQL While Keeping One Record?

Delete duplicate rows (duplicate data) in MySQL

Question:

How to remove duplicate rows from a MySQL table while retaining a single record for each unique value?

Example:

Consider the following data in the names table:

Solution:

Warning: Always take the precaution of performing this operation on a test copy of the table first.

To remove all duplicate rows (keep one) and keep the record with the lowest or highest id value, use the following query:

Keep 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>

Keep 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>

Additional notes:

• Make sure to add the condition AND n1.id != n2.id to both queries to prevent accidentally deleting all rows.
• This method is computationally expensive and may break your connection if the table is large.
• For large tables, consider using INSERT with DISTINCT as a faster alternative:

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

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