Oracle table Data deduplication

How is duplicate data removed from the Oracle database? When working with data tables, there are often duplicate data, so how do you delete them? Let's talk about removing data duplication from Oracle databases. There are two ways we are going to talk today.

First, according to rowID come and go heavy.

We all know that in Oracle database tables, each record has a unique rowid to identify, which allows us to go to the heavy query condition

We have a test table, first of all, if we're going to press Id,value to go heavy,

DELETE TEST A
WHERE not EXISTS (SELECT 1
From (SELECT ID, VALUE, MAX (ROWID) Maxrowid
From TEST
GROUP by ID, VALUE) T
WHERE a.rowid = T.maxrowid);

Similarly, if you only weigh it by ID,

DELETE TEST A
WHERE not EXISTS (SELECT 1
From (SELECT ID, MAX (ROWID) Maxrowid
From TEST
GROUP by ID) T
WHERE a.rowid = T.maxrowid);

Two. Use Row_number () over (PARTITION by column ORDER by column) to remove the weight of the window function

This is an open window function, Row_number () returns a number for each grouped record starting at 1.

If we want to go heavy by ID, the one with the smallest value of the same ID, according to Row_number () we can get,

At this point, we can take the corresponding value according to the identification of the Row_number () production.

DELETE from AAAA T1
WHERE ROWID not in (SELECT RID
From (SELECT T1,*,rowid as RIDs,
Row_number () over (PARTITION by T1.id ORDER by T1. VALUE) as RN
From AAAA T1)
WHERE RN = 1);

Oracle table Data deduplication