Several SQL statements used to delete Repeated Records

1. Use the rowid Method

2. Use the group by Method

3. Use the distinct method

 

1. Rowid Method

The statement is as follows:
Query data:

Select * from table1 a where rowid! = (Select max (rowid)
From table1 B where a. name1 = B. name1 and a. name2 = B. name2 ......)

Delete data:

Delete from table1 a where rowid! = (Select max (rowid)
From table1 B where a. name1 = B. name1 and a. name2 = B. name2 ......)

2. group by Method

Query data:
　

Select count (num), max (name) from student -- lists the number of repeated records and its name attribute
Group by num
Having count (num)> 1 -- group by num to find the num column in the table, that is, more than once

Delete data:
　

Delete from student
Group by num
Having count (num)> 1

In this way, all duplicates are deleted.

3. Use the distinct method-useful for small tables

Create table table_new as select distinct * from table1 minux
Truncate table table1;
Insert into table1 select * from table_new;