MySQL deletes duplicate data from 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: |
Copy code |
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: |
Copy code |
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: |
Copy code |
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: |
Copy code |
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
The Code is as follows: |
Copy code |
Delete from vitae Where (a. peopleId, a. seq) in (select peopleId, seq from vitae group by peopleId, seq having count (*)> 1) And rowid not in (select min (rowid) from vitae group by peopleId, seq having count (*)> 1) |