Oracle searches for and deletes duplicate records in a table

Case: one field in an application table is the primary key. when inserting data into the table, the data is first put in the temporary table (without the primary key) and then inserted into 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, procedure

1. query duplicate data in the table

A. Repeat a field

B. Repeat Multiple Fields

C. Repeat the entire line

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 are 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 ');

In case of a, only businessid is repeated.

Select * from CFA where businessid in (select businessid from CFA group by businessid having count (businessid)> 1 );

In the case of B, both businessid and name are repeated.

Select * from CFA where (businessid, customer) in (select businessid, customer from CFA group by businessid, customer having count (*)> 1 );

For C, repeat the entire line.

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 in the table

In case a, the redundant duplicate records in the table are deleted. The duplicate records are determined based on a single field (businessid), leaving only the records with the smallest rowid

You can also keep the rowid instead of the minimum record.CodeHere we will not repeat the change of min in to Max.

Delete from CFA
Where businessid in (select businessid
From CFA
Group by businessid
Having count (businessid)> 1)
And rowid not in (select Min (rowid)
From CFA
Group by businessid
Having count (businessid)> 1 );

Alternatively, use the following simple and efficient statements:

Delete from CFA t
Where T. rowid>
(Select Min (X. rowid) from cfa x where X. businessid = T. businessid );

B. Delete the redundant record (multiple fields) in the table and only keep the records with the smallest rowid.

Delete from CFA
Where (businessid, customer) in (select businessid, customer
From CFA
Group by businessid, customer
Having count (*)> 1)
And rowid not in (select Min (rowid)
From CFA
Group by businessid, customer
Having count (*)> 1 );

Alternatively, use the following simple and efficient statements:

Delete from CFA t
Where T. rowid> (select Min (X. rowid)
From CFA x
Where X. businessid = T. businessid
And X. Customer = T. Customer );

C. This is simple. Use the temporary table method.

Create Table cfabak as select distinct * from CFA;

Truncate table CFA; -- it is best to back up the table for Production

Insert into CFA select * From cfabak;

Commit;