Procedure for Oracle to search for and delete duplicate records in a 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:

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
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.
Copy codeThe Code is as follows:
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.

Refer to Method B:
Copy codeThe Code is as follows:
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. You need to change the value of min in the code to max.

Copy codeThe Code is as follows:
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:

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
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:

Copy codeThe Code is as follows:
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.

Copy codeThe Code is as follows:
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;