How do I delete repeated records in a database?

First, the outside chapter

Today very sad urge Ah, to user data to do datapatch, every one months of data imported a copy, instantly startled out in a cold sweat ... This but the product environment, if the boss knows, it will be dead. Quickly remove the repeated records, the same time write down the following articles for later use.

Ii. Preparation of the article

1. First create a student table student:

CREATE TABLE student (       ID varchar () NOT NULL,       name varchar (TEN) is not NULL, and age number is not null       );

2. Insert several data into table student:

INSERT into student values (' 1 ', ' zhangs ', ' + '), insert into student values (' 1 ', ' zhangs ', ' + '), insert into student values (' 2 ' , ' zhangs ', insert into student values (' 3 ', ' Lisi ', ' + '), insert into student values (' 4 ', ' Lisi ', +); INSERT INTO Studen T values (' 5 ', ' Wangwu ', 30);

Third, deal with the article

1. Using rowID

① query:

SELECT * FROM  student S1 where ROWID! = (select Max (ROWID) from                   student s2                  where s1.id = S2.id and                    s1.name = S2.name and                    s1.age = s2.age)

Note: ROWID is a unique identifier that records the physical location of an ID, and the parentheses are the largest rowid in the query for repeated data.

② Delete:

Delete from student S1 where ROWID! = (select Max (ROWID) from                   student s2                  where s1.id = s2.id and                    s1.name = s2. Name and                    s1.age = s2.age)

2. Use GROUP BY and having

① query:

Select ID, name, age, COUNT (*) from the  student group by ID, name, agehaving count (*) > 1;

② Delete:

Delete from student where rowID in (select min (rowid) from                   student                  Group by ID, name, age have                 count (*) > 1)

3. Using distinct

CREATE TABLE Stud_temp as SELECT DISTINCT * from student; --Create temporary tables stud_temptruncate table student; --Empty Student table INSERT into student select * from Stud_temp; --Import the temporary table data into the Student table drop tables stud_temp; --Delete a temporary table

Note: Distinct is only suitable for small table processing, assuming that the Tens data table, use ROWID, because it is unique, more efficient.

How do I delete repeated records in a database?