Ways to find and delete duplicate data in MySQL

Source: Internet
Author: User
Tags rowcount table name

(i) a single field

1, look up the redundant records in the table, according to (Question_title) field to judge

The code is as follows Copy Code
SELECT * from questions where Question_title in (select Question_title to People GROUP by Question_title has count (qu Estion_title) > 1)

2, delete redundant records in the table, according to (Question_title) field to judge, only left a record

The code is as follows Copy Code
Delete from questions
where Peopleid in (select Peopleid from People GROUP by Peopleid has count (question_title) > 1)
and min (id) not in (select question_id from questions GROUP by Question_title has count (question_title) >1)

(ii) Multiple fields

Delete extra duplicate records (multiple fields) in a table, leaving only the smallest ROWID records

The code is as follows Copy Code

DELETE from Questions WHERE (Questions_title,questions_scope) in (SELECT Questions_title,questions_scope from questions GROUP by Questions_title,questions_scope has COUNT (*) > 1) and question_id not in (SELECT MIN. question_id) from ques tions GROUP by Questions_scope,questions_title has COUNT (*) >1)

Can not be deleted with the above statement, create a temporary table to delete, please everyone to explain.

The code is as follows Copy Code

CREATE TABLE tmp as select question_id from questions WHERE (Questions_title,questions_scope) in (select Questions_title,q Uestions_scope from questions GROUP by Questions_title,questions_scope has COUNT (*) > 1) and question_id not in (SEL ECT MIN (question_id) from questions GROUP by Questions_scope,questions_title has COUNT (*) >1);

DELETE from questions WHERE question_id in (SELECT question_id from TMP);

DROP TABLE tmp;

(iii) Stored procedures

The code is as follows Copy Code

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

Begin

Select @max = @max-1

SET ROWCOUNT @max

Delete from table name where main field = @id

Fetch cur_rows into @id, @max

End

Close Cur_rows

SET ROWCOUNT 0

Cases

Database version Server Version:5.1.41-community-log MySQL Community Server (GPL)

Example 1, the table has a primary key (a field that can be uniquely identified), and the field is a numeric type

Example 1 Test data

The code is as follows Copy Code

/* Table Structure * *
DROP TABLE IF EXISTS ' T1 ';
CREATE TABLE IF not EXISTS ' T1 ' (
' ID ' INT (1) Not NULL auto_increment,
' Name ' VARCHAR not NULL,
' Add ' VARCHAR not NULL,
PRIMARY KEY (' id ')
) Engine=innodb;

/* INSERT TEST data * *
INSERT into ' t1 ' (' name ', ' Add ') VALUES
(' abc ', ' 123 '),
(' abc ', ' 123 '),
(' abc ', ' 321 '),
(' abc ', ' 123 '),
(' Xzy ', "123"),
(' Xzy ', "456"),
(' Xzy ', "456"),
(' Xzy ', "456"),
(' Xzy ', "789"),
(' Xzy ', "987"),
(' Xzy ', "789"),
(' Ijk ', "147"),
(' Ijk ', "147"),
(' Ijk ', "852"),
(' Opq ', "852"),
(' Opq ', "963"),
(' OPQ ', "741"),
(' TPK ', "741"),
(' TPK ', "963"),
(' TPK ', "963"),
(' Wer ', "546"),
(' Wer ', "546"),
(' Once ', "546");

SELECT * from ' T1 ';
+----+------+-----+
| ID | name | Add |
+----+------+-----+
| 1 | ABC | 123 |
| 2 | ABC | 123 |
| 3 | ABC | 321 |
| 4 | ABC | 123 |
| 5 | Xzy | 123 |
| 6 | Xzy | 456 |
| 7 | Xzy | 456 |
| 8 | Xzy | 456 |
| 9 | Xzy | 789 |
| 10 | Xzy | 987 |
| 11 | Xzy | 789 |
| 12 | Ijk | 147 |
| 13 | Ijk | 147 |
| 14 | Ijk | 852 |
| 15 | OPQ | 852 |
| 16 | OPQ | 963 |
| 17 | OPQ | 741 |
| 18 | TPK | 741 |
| 19 | TPK | 963 |
| 20 | TPK | 963 |
| 21 | Wer | 546 |
| 22 | Wer | 546 |
| 23 | Once | 546 |
+----+------+-----+
Rows in Set (0.00 sec)

Find duplicate data with least ID (Find ID field only)

The code is as follows Copy Code

/* Find duplicate data with minimal ID (Find ID field only) * *
SELECT DISTINCT MIN (' id ') as ' id '
From ' T1 '
GROUP by ' name ', ' Add '
Having COUNT (1) > 1;
+------+
| ID |
+------+
| 1 |
| 12 |
| 19 |
| 21 |
| 6 |
| 9 |
+------+
Rows in Set (0.00 sec)


Find all duplicate data

  code is as follows copy code
/* Find all duplicate data */
Select ' T1 '. *
from ' T1 ', (
  SELECT ' name ', ' Add '
  to ' T1 '
  GROUP by ' name ', ' Add '
&N Bsp Having COUNT (1) > 1
) as ' T2 '
WHERE ' t1 '. ' Name ' = ' T2 '. ' Name '
  and ' T1 '. ' Add ' = ' t2 '. ' Add ';
+---- +------+-----+

| id | name | add |
+----+------+-----+
|  1 | abc  | 123 |
|  2 | abc  | 123 |
|  4 | abc  | w Ww.111cn.net|
|  6 | xzy  | 456 |
|  7 | xzy  | 456 |
|  8 | xzy  | 456 |
|  9 | xzy  | 789 |
| xzy  | 789 |
| ijk  | 147 |
| ijk  | 147 |
| tpk  | 963 |
| tpk  | 963 |
| wer  | 546 |
| wer  | 546 |
+----+------+-----+
Rows in Set (0.00 sec)

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.