Mysql implements deduplication of data

This article mainly shares with you how to delete duplicate data in mysql. This article explains it with examples and hopes to help you.

delete FROM `ex` WHERE phone in (SELECT c.phone
FROM `ex` as c
GROUP BY c.phone

HAVING COUNT( * ) > ;1) and id not in (select max(b.id) from `ex` as b group by b.phone);

Note: ex (example table name), determine whether it is duplicated based on the phone field . Repeatedly retain the latest inserted one (that is, the one with the largest auto-incremented ID)

However, after execution, the result is not as you expected:

/* SQL error (1093): You can't specify target table 'ex' for update in FROM clause */

Translation: You cannot select certain values ​​in the same table first, and then update (delete) this table (in the same statement), that is You cannot make a judgment based on the value of a certain field and then update the value of a certain field.

The question is only for: mysql, not including oracle, sql server, etc. Because mysql's support for substatements is not very good.

problem solved:

Select the result first and then select it again through the intermediate table. The result of the select becomes a cached table with an alias, a virtual table.

As shown in the figure: This solves the problem of deleting duplicate information in one SQL statement of mysql.

For everyone’s convenience:

DELETE  FROM   `ex`   WHERE  id NOT IN (  SELECT  id  FROM  (SELECT max(b.id) AS id  FROM `ex`  b  GROUP BY b.phone) b);

Related recommendations:

Mysql deletes duplicate data and keeps minimum id

Delete duplicate data sql statement

MySQL deletes duplicate data rows and saves only one

The above is the detailed content of Mysql implements deduplication of data. For more information, please follow other related articles on the PHP Chinese website!