The database deletes records that are completely duplicated and that are repeated in some key fields.

1. The first type of repetition is easy to solve. The methods in different database environments are similar:

Reference content is as follows:
Mysql

Create table tmp select distinct * from tableName;

Drop table tableName;

Create table tableName select * from tmp;

Drop table tmp;


SQL Server

Select distinct * into # Tmp from tableName;

Drop table tableName;

Select * into tableName from # Tmp;

Drop table # Tmp;

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 design of the table is not weekly. Adding a unique index column can solve this problem.

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.

Mysql

Reference content is as follows:
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;

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;

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 a result set with no duplicate Name and Address (an autoID field is added, which can be omitted in the select clause when you actually write)

2. because MySQL and SQL Server do not provide the rowid mechanism, you need to use an autoID column to implement row uniqueness. Oracle's rowid processing is much more convenient. ROWID is the most efficient way to delete duplicate records.