How does an Oracle database Delete redundant duplicate data ?, Oracle Database

How does an Oracle database Delete redundant duplicate data ?, Oracle Database

This problem is often encountered in Oracle. There is a table a, where multiple data with different IDs but the same information exists. The requirement is to delete redundant duplicate data.

1. Prepare the table test

create table test(id number not null primary key, day date not null); 

2. Prepare data

insert into test(id, day) values(4, to_date('2006-10-08','yyyy-mm-dd'));  insert into test(id, day) values(5, to_date('2006-10-08','yyyy-mm-dd'));  insert into test(id, day) values(6, to_date('2006-10-10','yyyy-mm-dd'));  insert into test(id, day) values(7, to_date('2006-10-08','yyyy-mm-dd'));  insert into test(id, day) values(8, to_date('2006-10-10','yyyy-mm-dd'));  insert into test(id, day) values(9, to_date('2006-10-11','yyyy-mm-dd'));  insert into test(id, day) values(10, to_date('2006-10-11','yyyy-mm-dd'));  insert into test(id, day) values(11, to_date('2006-10-12','yyyy-mm-dd'));  insert into test(id, day) values(12, to_date('2006-10-12','yyyy-mm-dd')); 

Next we will solve this problem through the most common grouping function method.

The core is to delete an Oracle Rowid. (Oracle is indeed powerful and provides the concept of pseudo columns. A pseudo column uniquely identifies a data entry during an insert operation .)

Not much nonsense, dry goods:

 

delete from test awhere a.day in(select day from test group by day having count(*) > 1)and rowid not in(select min(rowid) from test group by day having count(*) > 1)

The preceding is the result SQL code. This code can delete redundant duplicate data.

Core code:

Where a. day in (select day from test group by day having count (*)> 1) grouping the current table to find duplicate items.

and rowid not in(select min(rowid) from test group by day having count(*) > 1)

Duplicate items are found, and the rowid of the pseudo column is used to display the minimum (or maximum) pseudo columns while repeating. You can delete a column with a unique identifier.