Oracle searches for and deletes duplicate records in a table

Oracle searches for and deletes repeated records in a table: a field in an application table is a primary key. when inserting data into a table, the data is first placed in a temporary table (without a primary key) then insert the application table. At this time, if duplicate data exists in the temporary table, whether the primary key field businessid is repeated or the whole row is duplicated, an error in violation of the unique primary key constraint will be reported. Method: group by XX having count (*)> 1, rowid, distinct, temporary table, procedure1, query duplicate data in table. repeat a field B. repeat multiple fields c. repeat the entire row to create a test table: create table cfa (businessid number, customer varchar2 (50), branchcode varchar2 (10), data_date varchar2 (10); insert into cfa values (1, 'Albert ', 'scb', '2017-11-11'); insert into cfa values (2, 'andy ', 'db', '2017-11-12 '); insert into cfa values (3, 'allen ', 'HSBC', '2017-11-13 '); ------------- The following is a duplicate Data ------------------------------------------ insert into cfa values (1, 'Alex ', 'icbc', '2017-11-14'); insert into cfa values (1, 'Albert ', 'ctbk ', '2017-11-15 '); insert into cfa values (1, 'Albert', 'scb', '2017-11-11 '); for, only businessid repeated select * from cfa where businessid in (select businessid from cfa group by businessid having count (businessid)> 1); if it is B, both businessid and name have repeated select * from cfa wher E (businessid, customer) in (select businessid, customer from cfa group by businessid, customer having count (*)> 1); for c, to repeat the entire row, see Method B: select * from cfa where (businessid, customer, branchcode, data_date) in (select * from cfa group by businessid, customer, branchcode, data_date having count (*)> 1); 2. Delete duplicate data a in the table and delete redundant duplicate records in the table. duplicate records are determined based on a single field (businessid, only records with the smallest rowid can be retained, and rowid is not the smallest record. You need to change the min in the code to max. Delete from cfawhere businessid in (select businessidfrom cfagroup by businessidhaving count (businessid)> 1) and rowid not in (select min (rowid) from cfagroup by businessidhaving count (businessid)> 1 ); alternatively, use the following simple and efficient statement delete from cfa tWHERE t. ROWID> (select min (X. ROWID) FROM cfa x where x. businessid = t. businessid); B, delete unnecessary duplicate records (multiple fields) in the table, with only the records with the smallest rowid deleted from cfawhere (businessid, customer) in (select businessid, customerfrom cfagroup by businessid, customerhaving count (*)> 1) and rowid not in (select min (rowid) from cfagroup by businessid, customerhaving count (*)> 1); or, use the following simple and efficient statement delete from cfa tWHERE t. ROWID> (select min (X. ROWID) FROM cfa xwhere x. businessid = t. businessidand x. customer = t. customer); c, this situation is relatively simple, use the temporary table method create table cfabak as select distinct * from cfa; truncate table cfa; -- for production, it is best to perform backupInsert into cfa select * from cfabak; commit;