delete duplicate data and only keep one in MySQL

created at 07-27-2021 views: 3

background

In the tens of thousands of records in the people table, there are some identical records with the field bit: gci_id.

need

How to use SQL statements to obtain duplicate data and delete duplicate data, and only keep one

implemention

1. Find redundant duplicate records in the table, duplicate records are judged based on a single field (gci_id)

select * from people
where id in (select id from people group by gci_id having count(gci_id) > 1)

The having statement is usually used in conjunction with the GROUP BY statement to filter the set of records returned by the GROUP BY statement.

The existence of the having statement makes up for the lack of keywords that cannot be used in conjunction with aggregate functions.

2. Delete redundant duplicate records in the table. Duplicate records are judged based on a single field (peopleId), leaving only the record with the smallest rowid

delete from people
where   name in (select name from people group by gci_id having count(gci_id) > 1)
and   id not in (select min(id) from people group by gci_id having count(gci_id)>1)

This sentence of sql means to extract all the data of the repeated name of gcd_id, and filter out the smallest value of id among the repeated gcd_id. Then delete this data

3. Find redundant duplicate records in the table (multiple fields)

select id,name,gci_id from people group by name,gci_id having count(*) > 1

Query duplicate data of name and gci_id

4. Delete redundant duplicate records (multiple fields) in the table, leaving only the record with the smallest rowid

delete from people
where (name,gci_id) in (select name,gci_id from people group by name,gci_id having count(*) > 1)
and id not in (select min(id) from people group by name,gci_id having count(*)>1)

Note: after the where statement (name, gci_id) must be bracketed

created at:07-27-2021
edited at: 07-27-2021: