Duplicate records in MySQL query table

Source: Internet
Author: User
Tags mysql query rowcount


1, look for redundant records in the table, duplicate records are based on a single field (Peopleid) to determine the select * from Peoplewhere peopleid in (select Peopleid from people GROUP by Peopleid Having count (Peopleid) > 1) 2, delete redundant duplicate records in the table, repeat records are based on a single field (Peopleid) to judge, leaving only one record delete from Peoplewhere Peopleid in ( Select Peopleid from People GROUP by Peopleid have count (Peopleid) > 1) and min (id) not in (select ID from people Grou P by Peopleid have count (Peopleid) >1) 3, redundant duplicate records in lookup table (multiple fields) SELECT * from Vitae awhere (A.PEOPLEID,A.SEQ) in (select PE Opleid,seq from Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) 4, delete redundant duplicate records (multiple fields) in the table, leaving only rowid minimum records delete from Vitae AWH Ere (A.PEOPLEID,A.SEQ) in (select Peopleid,seq to Vitae GROUP by PEOPLEID,SEQ have count (*) > 1) and rowID not in (s Elect min (ROWID) from Vitae GROUP by PEOPLEID,SEQ have Count (*) >1) 5, find redundant duplicate records in the table (multiple fields), do not contain ROWID minimum records SELECT * from Vit  AE Awhere (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) 



There is a field "name" in Table A, and the "name" value between different records may be the same, now it is necessary to query out the records in the table, the "name" value has duplicate entries;?
1 SelectName,Count(*) From A Group By Name HavingCount(*) > 1

If you also look at the same gender is the same large as follows:?
1 SelectName,sex,Count(*) From A Group By Name,sex HavingCount(*) > 1


Declare @max integer, @id integer

Declare cur_rows cursor Local for select main field, COUNT (*) from table name Group by main field having count (*) >; 1

Open Cur_rows

Fetch cur_rows into @id, @max

While @ @fetch_status =0


Select @max = @max-1


Delete from table name where main field = @id

Fetch cur_rows into @id, @max


Close Cur_rows


Duplicate records in MySQL query table

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.