MySQL deletes duplicate data from the database (subject to some data) _ MySQL

MySQL deletes duplicate data in the database (subject to some data) delete from zqzrdp

Where tel in (select min (dpxx_id) from zqzrdp group by tel having count (tel)> 1 );

Execution, error reported

Exception: you cannot specify the update of the target table in the FROM clause. It's silly. MySQL cannot be written like this, which is depressing.

However, you can only perform step-by-step operations.

The following code is also written by netizens. it cannot run on my machine.

1. query the records to be deleted. a record is retained.

The code is as follows:
Select. id,. subject,. else er from test1 a left join (select c. subject, c. cycler, max (c. id) as bid from test1 c where status = 0 group by receiver, SUBJECT having count (1)> 1) B on. id <B. bid where. subject = B. subject and. worker er = B. aggreger and. id <B. bid

2. delete duplicate records. only one record is retained. Note: subject and aggreger must be indexed. Otherwise, it will be slow.

The code is as follows:
Delete a from test1 a, (select c. subject, c. cycler, max (c. id) as bid from test1 c where status = 0 group by consumer er, SUBJECT having count (1)> 1) B where. subject = B. subject and. worker er = B. aggreger and. id <B. bid;

3. search for redundant duplicate records in the table. duplicate records are determined based on a single field (peopleId ).

The code is as follows:
Select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1)

4. delete unnecessary duplicate records in the table. Repeat records are determined based on a single field (eagleid), leaving only the records with the smallest rowid

The code is as follows:
Delete from people Where peopleId in (select peopleId from people group by peopleId having count (peopleId)> 1) And rowid not in (select min (rowid) from people group by peopleId having count (peopleId)> 1)

5. delete redundant record (multiple fields) in the table, with only the records with the smallest rowid