Duplicate data deletion methods in Oracle databases: partial to heavy + total to heavy

How to remove duplicate data from an Oracle database. When you use a datasheet, you often get duplicate data, so how do you delete it? The following green Tea Small series for you to share the resolution of Oracle database data duplication problem.

Oracle database duplication of data generally have two ways to weight, one, complete duplication of data, two, some of the field data duplication to heavy.

a completely repetitive data-weight method

For full duplicate data in a table, you can use the following SQL statement.

Code

CreateTable "#temp" as (selectdistinct * from table name);--Create a temporary table and insert the distinct data back into a temporary table

truncatetable table name;--Empty the original table data

Insertinto Table Name (SELECT * from "#temp");--insert temporary table data into the original table

Droptable "#temp";--Delete temporary table

The idea is to first create a temporary table, then insert the table data after the distinct into the temporary table, and then empty the original table data, then insert the data from the temporary table into the original table, and then delete the temporary table.

second, part of the data to weight method

Find duplicate Data first

Select field 1, Field 2,count (*) from table name groupby field 1, Field 2 havingcount (*) > 1

Change the above > number to = number to query for no duplicate data.

To delete these duplicate data, you can delete it using the following statement:

Deletefrom table name a WHERE field 1, Field 2 in

(select field 1, Field 2,count (*) from table name groupby field 1, Field 2 havingcount (*) > 1)

The above statement is very simple, that is, the query to delete the data. However, the efficiency of this deletion is very low, and for large amounts of data, the database may be stuck to death.

Based on the above, you can insert the duplicate data from the query into a temporary table, and then delete it, so that you do not have to do a query again when you perform the deletion. As follows:

CreateTable temporary table as

(select field 1, Field 2,count (*) from table name groupby field 1, Field 2 havingcount (*) > 1)

The following can be done with this delete operation:

Deletefrom table name a WHERE field 1, Field 2 in (Select field 1, Field 2 from temporary table);

It is much more efficient to build a temporary table before deleting it than to delete it directly with a single statement.

The above statement will remove all duplicates. In Oracle, there is a hidden automatic rowid, which gives each record a unique rowid, and if we want to keep the latest record, we can use this field to keep the rowid largest record in the duplicate data.

Here is an example of querying for duplicate data:

Select a.rowid,a.* from table name a

where A.rowid!=

(

Selectmax (B.ROWID) from table name B

Where a. Field 1 = B. Field 1 and

A. Field 2 = B. Field 2

)

The statement in parentheses above is the largest record in the query for duplicate data in ROWID. And the outside is to query out other than ROWID the largest number of other duplicate data.

So, we're going to delete the duplicate data and just keep the latest one, so we can write this:

Deletefrom Table Name A

where A.rowid!=

(

Selectmax (B.ROWID) from table name B

Where a. Field 1 = B. Field 1 and

A. Field 2 = B. Field 2

)

In the same vein, the execution efficiency of the above code is low, so we can consider setting up a temporary table that will need to judge the duplicate fields, ROWID into the temp table, and then compare them when we delete them.

CreateTable temporary table as

Select a. field 1,a. Field 2,max (A.rowid) dataid from official table A GROUPBY a. field 1,a. field 2;

Deletefrom Table Name A

where A.rowid!=

(

Select B.dataid from temporary table B

Where a. Field 1 = B. Field 1 and

A. Field 2 = B. Field 2

);

Commit

All right. The method of repeated deletion of Oracle database data for everyone to introduce here, we try to delete duplicate data bar.

Green Tea Small Series Guess you also like:

Oracle Database Deadlock Solution

Oracle Database ora 01034 Error problem resolution