Oracle Delete duplicate Data _oracle

Duplicate data can be in two cases, the first: only some of the fields in the table, and the second is exactly the same as two rows of records.
one, for partial field duplicate data deletion
1. Query for 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 Count (*) >1;
Select owner from Dba_tables Group By Owner count (*) = 1; Query for no duplicate data
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)
This deletion is very inefficient and may hang the database for large amounts of data.
Another efficient method is to insert the duplicate data from the query into a temporary table before deleting it.
CREATE Table temporary table as
(
Select field 1, Field 2, COUNT (*) as Row_num
From table name
Group By field 1, Field 2
Having count (*) > 1
);
The above sentence is to create a temporary table, and the query to insert the data.
The following can be done with this delete operation:
Delete from table name a
Where field 1, Field 2 in (Select field 1, Field 2 from temporary table);
3. Keep the latest record in duplicate data
In Oracle, ROWID is a hidden field used to uniquely identify each record. So, just keep the largest record of ROWID in duplicate data.
Query for duplicate data:
Select a.rowid,a.* from table name a
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 a
Where a.rowid!= (
Select Max (ROWID) from Test b
where A.owner=b.owner);
Delete duplicate data, keeping only the most recent data:
Delete from table name a
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)
Implementing efficient queries with temporary tables
CREATE table temporary table as
(Select a. Field 1, A. Field 2, MAX (A.rowid) as dataid from official table A
GROUP by a. Field 1,a. Field 2);
Delete from 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
ii. Deletion of full duplicate records
For two rows in a table that are identical, you can get the record after you remove the duplicate data by using the following statement:
SELECT DISTINCT * FROM table name
You can place the records of the query in a temporary table, then delete the original table records, and finally return the data from 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 form (SELECT * from temporary table);
drop table temporary table; If you want to delete duplicate data for a table, you can build a temporary table, import data from the duplicate data into a temporary table, and then import the data from the temporary table into the formal table as follows: INSERT into T_table_bak
SELECT DISTINCT * from t_table;

The following are supplementary:

Query for duplicate data in an Oracle database:

SELECT * FROM Employee GROUP BY Emp_name has count (*) >1;

Duplicate data that Oracle queries can delete

Select t1.* from Employee T1 where (t1.emp_name) in (SELECT t2.emp_name from Employee T2 GROUP by Emp_name has count (* >1) and t1.emp_id not in (select min (t3.emp_id) from employee T3 GROUP by Emp_name have Count (*) >1);

Oracle Delete duplicate data

Delete from employee T1 where (t1.emp_name) in (SELECT T2.emp_name to employee T2 GROUP by Emp_name have count (*) > 1) and t1.emp_id not in (select min (t3.emp_id) from the employee T3 group by Emp_name have Count (*) >1);