How to Efficiently Delete Duplicate Rows in a MySQL Table?

How to delete duplicate rows in MySQL table

In database management, eliminating duplicate data is a common task. In MySQL, this can be achieved using the DELETE command.

The following is a sample table:

<code class="language-sql">CREATE TABLE names (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (id)
);</code>

Suppose the table contains duplicates:

<code class="language-sql">INSERT INTO names (name) VALUES ('google');
INSERT INTO names (name) VALUES ('yahoo');
INSERT INTO names (name) VALUES ('msn');
INSERT INTO names (name) VALUES ('google');
INSERT INTO names (name) VALUES ('google');
INSERT INTO names (name) VALUES ('yahoo');

SELECT * FROM names;</code>

To remove all duplicate rows (keep the row with the smallest ID), use the following query:

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

Alternatively, to keep the row with the largest ID, use the following query:

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

Warning: Be sure to back up your tables before executing any delete queries to avoid accidental data loss.

For large tables, it is recommended to use the faster INSERT and DISTINCT methods:

```sql CREATE TEMPORARY TABLE temp_names SELECT DISTINCT id, name FROM names; TRUNCATE TABLE names; INSERT INTO names (id, name) SELECT id, name FROM temp_names; DROP TABLE temp_names; ```

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