Delete duplicate records

There are two Repeated Records. One is a completely repeated record, that is, records with all fields being repeated, and the other is records with duplicate key fields, such as duplicate name fields, other fields are not necessarily repeated or can be ignored.

1. For the first type of repetition, it is easier to solve, and the method is similar:
I) Mysql
Create Table TMP select distinct * From tablename;
Drop table tablename;
Create Table tablename select * from TMP;
Drop table TMP;

II) SQL Server
Select distinct * into # TMP from tablename;
Drop table tablename;
Select * into tablename from # TMP;
Drop table # TMP;

III) Oracle
Create Table TMP as select distinct * From tablename;
Drop table tablename;
Create Table tablename as select * from TMP;
Drop table TMP;

The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. Repeat problems usually require that the first record in the repeat record be retained. The procedure is as follows. Assume that the duplicate fields are Name and address. You must obtain the unique result set of the two fields.
I) Mysql
Alter table tablename add autoid int auto_increment not null;
Create table tmp select min (autoID) as autoID from tableName group by Name, Address;
Create table tmp2 select tableName. * from tableName, tmp where tableName. autoID = tmp. autoID;
Drop table tableName;
Rename table tmp2 to tableName;

II) SQL Server
Select identity (int, 1, 1) as autoID, * into # Tmp from tableName;
Select min (autoID) as autoID into # Tmp2 from # Tmp group by Name, Address;
Drop table tableName;
Select * into tableName from # Tmp where autoID in (select autoID from # Tmp2 );
Drop table # Tmp;
Drop table # Tmp2;

III) Oracle
Delete from tableName t1 WHERE t1.ROWID> (select min (t2.ROWID) FROM tableName t2 WHERE t2.Name = t1.Name and t2.Address = t1.Address );

Note:
1. The last select in Mysql and SQL Server gets the result set with Name and Address not repeated (but an autoID field is added, which can be omitted in the select clause when writing)
2. Mysql and SQL Server do not provide the rowid mechanism. Therefore, you need to use an autoID column to implement row uniqueness. Oracle's rowid processing is much more convenient. ROWID is also used, which is also the most efficient method for deleting duplicate records.