Delete duplicate records

Delete duplicate records
Because data is manually input, duplicate data is often generated, and redundant data needs to be deleted. CREATE a test TABLE: create table dupes (id integer, name varchar (10); insert into dupes VALUES (1, 'Tom '); insert into dupes VALUES (2, 'allen '); insert into dupes VALUES (3, 'allen'); insert into dupes VALUES (4, 'Smith '); insert into dupes VALUES (5, 'Smith '); insert into dupes VALUES (6, 'Smith'); SELECT * FROM dupes; we can see that the data of "ALLEN" and "SMITH" is duplicated, currently, only one row of data with duplicate names is required in the table, and others are deleted. There are several ways to delete data. Three methods are described below. Method 1: use the same name and different IDs to determine. The SQL code is as follows: delete from dupes a where exists (SELECT 1 FROM dupes B WHERE. name = B. name AND. id> B. id); SELECT * FROM dupes; the execution result is as follows: Method 2: replace the id with ROWID. The SQL code is as follows: delete from dupes a where exists (SELECT 1 FROM dupes B WHERE. name = B. name AND. ROWID> B. ROWID); the execution result is as follows: method 3: generate a sequence number based on the name group through the analysis function, and then delete the data whose sequence number is greater than 1. The SQL code is as follows: DELETE FROM dupes a WHERE ROWID IN (SELECT rid FROM (SELECT ROWID AS rid, ROW_NUMBER () OVER (PARTITION BY name ORDER BY id) as seq FROM dupes) WHERE seq> 1); The execution result is the same as above.