How to delete repeated Oracle records

There are often repeated records in Oracle databases. "Oracle Repeated Records" have two meanings: Oracle Repeated Records. One is completely Repeated Records, that is, record where all fields are repeated. Second, record with duplicate key fields. For example, if the Name field is repeated, other fields may not be repeated or can be ignored.

1. For the first type of Oracle repeat record, it is easy to solve. You can use select distinct * from tableName to obtain the result set without repeated record.

If the table needs to delete duplicate records and retain one record), you can delete the record as follows:

Select distinct * into # Tmp
From tablenameddrop table tableNameselect * into tableName
From # Tmpdrop table # Tmp
The reason for this repetition is that the table design is not weekly. You can add a unique index column.

2. This type of Oracle repeat record usually requires that the first record in the repeat record be retained. The procedure is as follows:

Assume that the duplicate fields are Name and Address. You must obtain the unique result set of the two fields.
Select identity (int, 1, 1) as autoID, * into # Tmp
From tableNameselect min (autoID)
As autoID into # Tmp2
From # Tmp group by Name, autoIDselect *
From # Tmp where autoID in (select autoID from # tmp2)
The Name is obtained in the last select statement. The Address does not have duplicate result sets, but an autoID field is added. This column can be omitted in the select clause during actual writing)

Implementation of oracle query current time

Learn more about Oracle hierarchical Query

Provides you with an in-depth understanding of Oracle temporary tables

Oracle with statement usage

Common ORACLE Data Types