How to Delete Duplicate Records in Oracle

When working in Oracle, you may find that some of your records have duplicates. You can delete these duplicate rows by identifying them and using its RowID, or row address. Before you begin, you should create a backup table in case you...

Method 1 of 4:

Identifying your Duplicate

  1. Picture 1 of How to Delete Duplicate Records in Oracle
    Identify the duplicate. In this case, identify the example duplicate, "Alan." Make sure that the records you are trying to delete are actually duplicates by entering the SQL below.
  2. Picture 2 of How to Delete Duplicate Records in Oracle
    Identifying from a column named "Names." In the instance of a column named "Names," you would replace "column_name" with Names.
  3. Picture 3 of How to Delete Duplicate Records in Oracle
    Identifying from other columns. If you were trying to identify the duplicate by a different column, for example the age of Alan rather than his name, you would enter "Ages" in the place of "column_name" and so on. 
    select column_name, count(column_name) from table group by column_name having count (column_name) > 1;
Method 2 of 4:

Deleting a Single Duplicate

  1. Picture 4 of How to Delete Duplicate Records in Oracle
    Select "name from names." After "SQL," which stands for Standard Query Language, enter "select name from names."
  2. Picture 5 of How to Delete Duplicate Records in Oracle
    Delete all of the rows with the duplicate name. After "SQL," enter "delete from names where name='Alan';." Note that capitalization is important here, so this will delete all of the rows named "Alan." After "SQL," enter "commit."[1]
  3. Picture 6 of How to Delete Duplicate Records in Oracle
    Renter the row without a duplicate. Now that you have deleted all rows with the example name "Alan," you can insert one back by entering "insert into name values ('Alan');." After "SQL," enter "commit" to create your new row.
  4. Picture 7 of How to Delete Duplicate Records in Oracle
    See your new list. Once you have completed the above steps, you can check to make sure you no longer have duplicate records by entering "select * from names." 
    SQL > select name from names; NAME ------------------------------ Alan Carrie Tom Alan rows selected. SQL > delete from names where name='Alan'; rows deleted. SQL > commit; Commit complete. SQL > insert into names values ('Alan'); row created. SQL > commit; Commit complete. SQL > select * from names; NAME ------------------------------ Alan Carrie Tom rows selected.
Method 3 of 4:

Deleting Multiple Duplicates

  1. Picture 8 of How to Delete Duplicate Records in Oracle
    Select the RowID you want to delete. After "SQL," enter "select rowid, name from names;."
  2. Picture 9 of How to Delete Duplicate Records in Oracle
    Delete the duplicate. After "SQL," enter "delete from names a where rowid > (select min(rowid) from names b where b.name=a.name);" to delete duplicate records.[2]
  3. Picture 10 of How to Delete Duplicate Records in Oracle
    Check for duplicates. After you have completed the above, commands check to see if you still have duplicate records by entering "select rowid,name from names;" and then "commit." 
    SQL > select rowid,name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAB Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom AABJnsAAGAAAdfOAAF Alan rows selected. SQL > delete from names a where rowid > (select min(rowid) from names b where b.name=a.name ); rows deleted. SQL > select rowid,name from names; ROWID NAME ------------------ ------------------------------ AABJnsAAGAAAdfOAAA Alan AABJnsAAGAAAdfOAAC Carrie AABJnsAAGAAAdfOAAD Tom rows selected. SQL > commit; Commit complete.
Method 4 of 4:

Deleting Rows with Columns

  1. Picture 11 of How to Delete Duplicate Records in Oracle
    Select your rows. After "SQL," enter "select * from names;" to see your rows.
  2. Picture 12 of How to Delete Duplicate Records in Oracle
    Delete duplicate rows by identifying their column. After "SQL'" enter "delete from names a where rowid > (select min(rowid) from names b where b.name=a.name and b.age=a.age);" to delete the duplicate records.[3]
  3. Picture 13 of How to Delete Duplicate Records in Oracle
    Check for duplicates. Once you have completed the above steps, enter "select * from names;" and then "commit" in order to check that you have deleted the duplicate records successfully. 
    SQL > select * from names; NAME AGE ------------------------------ ---------- Alan 50 Carrie 51 Tom 52 Alan 50 rows selected. SQL > delete from names a where rowid > (select min(rowid) from names b where b.name=a.name and b.age=a.age ); row deleted. SQL > select * from names; NAME AGE ------------------------------ ---------- Alan 50 Carrie 51 Tom 52 rows selected. SQL > commit; Commit complete.
Update 05 March 2020
namenamessqlselect

You should read it

Maybe you are interested

Category

System

Mac OS X

Hardware

Game

Tech info

Technology

Science

Life

Application

Electric

Program

Mobile