Oracle deletes duplicate data

Duplicate data may be in the following two cases: the first is that only some fields in the table are the same, and the second is that the records in the two rows are the same.
1. Deletion of duplicated data in some fields
1. query duplicate data
Select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1
Example: Select owner from dba_tables group by owner having count (*)> 1;
Select owner from dba_tables group by owner having count (*) = 1; // No duplicate data is found.
2. Delete duplicate data
Delete from table name a where field 1, Field 2 in (select Field 1, Field 2, count (*) from table name group by field 1, Field 2 having count (*)> 1)
The efficiency of such deletion execution is very low. For large data volumes, the database may be suspended.
Another efficient method is to insert the queried duplicate data into a temporary table before deleting it.
Create table temporary TABLE
(
Select Field 1, Field 2, count (*) as row_num
From table name
Group by field 1, Field 2
Having count (*)> 1
);
The above statement creates a temporary table and inserts the queried data into it.
You can perform the following deletion operations:
Delete from table name
Where Field 1, Field 2 in (select Field 1, Field 2 from temporary table );
3. Keep the latest record in the duplicate data
In Oracle, rowid is a hidden field that uniquely identifies each record. Therefore, you only need to keep the record with the largest rowid In the duplicate data.
Query duplicate data:
Select a. rowid, a. * from table name
Where a. rowid! = (
Select max (B. rowid) from table name B
Where a. Field 1 = B. Field 1 and a. Field 2 = B. Field 2 );
Example: selete from dba_tables
Where a. rowid! = (
Select max (rowid) from test B
Where a. owner = B. owner );
Delete duplicate data. Only the latest data record is retained:
Delete from table name
Where a. rowid! = (
Select max (B. rowid) from table name B
Where a. Field 1 = B. Field 1 and a. Field 2 = B. Field 2)
Efficient query using temporary tables
Create table temporary table
(Select a. Field 1, a. Field 2, MAX (a. ROWID) as dataid from formal Table
Group by a. Field 1, a. Field 2 );
Delete from table name
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;
Ii. Deletion of completely Repeated Records
If the two rows in the table have identical records, you can use the following statement to obtain the records after deduplication:
Select distinct * from Table Name
You can place the queried records in a temporary table, delete the original table records, and export the data in the temporary table back to the original table. As follows:
Create table temporary table as (select distinct * from TABLE Name );
Drop table formal table;
Insert into formal table (select * from temporary table );
Drop table temporary table; if you want to delete the duplicate data of a table, you can create a temporary table to import the data that has been removed from the duplicate data to the temporary table, then, import data from the temporary table to the formal table, as shown in the following figure: insert into t_table_bak
Select distinct * from t_table;