Use of rowID--quickly delete duplicate records in a table

⑴ by creating a temporary table

You can lead the data into a temporary table, and then delete the data from the original table, and then return the data back to the original table, the SQL statement is as follows:

Sql>create table stu_tmp as select distinct* from Stu; sql>truncate table sut;

Empty table record

Sql>insert into Stu select * from Stu_tmp; Add data from a temporary table back to the original table

This approach can achieve requirements, but it is obvious that this approach is slow for a tens-logged table, which in production systems can be costly and not possible.

⑵ use ROWID to combine Max or min functions

Delete duplicate rows using rowID quick unique to determine duplicate rows combining the Max or Min functions.

Sql>delete from Stu A where rowid, select Max (B.rowid) from Stu B where a.no=b.no and a.name = B.name      = B.sex); Here max can use min as well or use the following statement

Sql>delete from Stu a where rowID < (select Max (B.rowid) from Stu B where a.no=b.no and a.name = b.name and A.sex = B.sex); If you change Max to Min, you need to change "<" to ">" in the previous where clause.

With the above method of thinking is basically the same, but the use of group by, reduce the dominant comparison conditions, improve efficiency.

Sql>delete from Stu where rowID isn't in (select Max (ROWID) from Stu T Group by t.no, T.name, t.sex);