MySQL deletes duplicate data in the database (subject to partial data)

Delete from ZQZRDP

where Tel in (select min (dpxx_id) from ZQZRDP GROUP BY Tel has count (tel) >1);

Execute, Error

The exception is: You cannot specify the update of the target table in the FROM clause. Silly, MySQL so write, not, let people depressed.

You can only step in, egg hurts

The following is written by netizens, the same is the code of the pit daddy, I can not run on the machine.

1. Query for records that need to be deleted, and a record is retained.

code is as follows	copy code
select a.id, A.subject,a.receiver from Test1 a LEFT join (select C.subject,c.receiver, max (c.id) as  bid from test1 C where status =0 GROUP by Receiver,subject have count (1) >1) b on a.id< b.bid where  a.subject=b.subject and a.receiver = b . RECEIVER and a.ID < b.bid

2. Delete duplicate records and keep only one record. Note that Subject,receiver is going to be indexed, otherwise it will be slow.

code is as follows	copy code
delete A From Test1 A, (select C.subject,c.receiver, max (c.id) as  bid from test1 C where status=0 GROUP by Receiver,subject have Count (1) >1) b where a.subject=b.subject and a.receiver = B.receiver and a.ID < b.bid;

3. Find redundant duplicate records in a table, and repeat records are judged by a single field (Peopleid)

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 Redundant records in a table, repeating records are based on a single field (Peopleid), leaving only the smallest rowID records

code is as follows	copy code
delete from people  where peopleid  in (select  peopleid  from people  group  by  peopleId& nbsp;  having  count (Peopleid) > 1) and rowID not in (select min (rowid) from  people  Group by P eopleid  having Count (Peopleid) >1)

5. Delete extra duplicate records (multiple fields) in a table, leaving only the ROWID minimum record

The code is as follows	Copy Code
Delete from Vitae a where (A.PEOPLEID,A.SEQ) in (select Peopleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (select min (rowid) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1)