Oracle record duplication Processing

Oracle repeat processing already has some simple SQL on the internet for use, but I feel there are some improvements.

Delete Table1 where rowid not in (
Select max (rowid) from Table1 group by col1, col2)

The preceding method deletes duplicate information and retains the record with the largest rowid in the same field.

However, this method needs to be improved to query numbers without duplicates. Readers may think that distinct can be used to deduplicate the number. However, you need to record the number again.

This article briefly introduces some SQL statements for Repeated Records, only for Oracle.

 

Query duplicate records:

Select * From yourtable where key in (

Select key from yourtable group by key having count (key)> 1)

Order by key

Query the result set for removing duplicate records:

Select *

From (select T. *, row_number () over (partition by key order by field1) rnk

From yourtable t

Order by key) ot

Where rnk = 1

Here, it is more flexible to rank the group information to retrieve records that should be retained. In the preceding example, key is grouped and sorted by field1 field to flexibly select useful information.

With query, it is easy to import duplicate records to another table:

Create Table another_table

Select .....

 

Back to question: What if I delete Repeated Records?

You can use the keyword of the query result as the deletion condition:

Delete table yourtable where key in (

Select key

From (select key, row_number () over (partition by key order by field1) rnk

From yourtable t

Order by key) ot

Where rnk> 1)

It seems much more complicated than the solution provided by others, but it only adds a flexible option for Repeated Records.

This is just another way of thinking. Please give your comments and correct them.