Everything You Need to Know About Removing Duplicate Records in Oracle

PDF download Download Article
Plus, easy steps to identify a duplicate in Oracle
PDF download Download Article

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 need to reference them after you have deleted records.

How to Remove Duplicate Records in Oracle

  1. To remove a single duplicate, select name from names .
  2. Enter delete from names where name='Alan' .
  3. Delete all rows named Alan , then enter commit .
  4. Enter insert into name values ('Alan'); then commit to create a new row.
  5. For multiple duplicates, enter select rowid, name from names; .
  6. Delete the duplicates and check for any remaining ones before selecting commit
Section 1 of 4:

Identifying your Duplicate

PDF download Download Article
  1. 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. In the instance of a column named "Names," you would replace "column_name" with Names. [1]
    Advertisement
  3. 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. [2] 
     select 
 column_name 
 , 
 count 
 ( 
 column_name 
 ) 
 from 
 table 
 group 
 by 
 column_name 
 having 
 count 
 ( 
 column_name 
 ) 
 > 
 1 
 ;
    4. Advertisement
Section 2 of 4:

Deleting a Single Duplicate

PDF download Download Article
  1. After "SQL," which stands for Standard Query Language, enter "select name from names."
  2. 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."
  3. 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. Once you have completed the above steps, you can check to make sure you no longer have duplicate records by entering "select * from names." [3] 
     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 
 .
    5. Advertisement
Section 3 of 4:

Deleting Multiple Duplicates

PDF download Download Article
  1. After "SQL," enter "select rowid, name from names;."
  2. After "SQL," enter "delete from names a where rowid > (select min(rowid) from names b where b.name=a.name);" to delete duplicate records. [4]
  3. 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 
 .
    4. Advertisement
Section 4 of 4:

Deleting Rows with Columns

PDF download Download Article
  1. After "SQL," enter "select * from names;" to see your rows.
  2. 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. [5]
  3. 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 
 .
    4. Advertisement

Expert Q&A

Ask a Question
200 characters left
Include your email address to get a message when this question is answered.
Submit
      Advertisement

      Tips

      Submit a Tip
      All tip submissions are carefully reviewed before being published
      Name
      Please provide your name and last initial
      Submit
      Thanks for submitting a tip for review!

      Warnings

      • Create a backup table in your own sign-in that you can use to show what was there before any delete occurred (in case there are any questions). 
         SQL 
 > 
 create 
 table 
 alan 
 . 
 names_backup 
 as 
 select 
 * 
 from 
 names 
 ; 
 Table 
 created 
 .
        Thanks
        Helpful 0 Not Helpful 0
      Advertisement

      You Might Also Like

      The Ultimate Guide to Using Microsoft Access
      How to
      Learn Data Entry
      How to
      Make a Database Using MS Access
      How to
      Reset SA Password in SQL Server
      2 Ways to Import Data from the Web into Excel: Windows & Mac
      How to
      Add Data to a Pivot Table
      How to
      Order Alphabetically in SQL
      How to
      Make an Inventory Database in Access: Complete Guide
      How to
      Link Tables in Access
      How to
      Create a Database from an Excel Spreadsheet
      How to
      Download Microsoft Access
      How to
      Hack a Database
      How to
      Check SQL Server Query Performance: Step-by-Step Guide
      How to
      Write Basic SQL Statements in SQL Server
      Advertisement

      References

      1. ↑ https://www.oracletutorial.com/advanced-oracle-sql/find-duplicate-records-oracle/
      2. ↑ https://www.oracletutorial.com/advanced-oracle-sql/find-duplicate-records-oracle/
      3. ↑ https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql
      4. ↑ https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql
      5. ↑ https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql

      About This Article

      Article Summary X

      To delete duplicate records in Oracle, start by making sure the records are actually duplicates by entering the Standard Query Language, or SQL. After entering “SQL,” search for what you want to delete, like “delete from names where name = ‘Alan.’” Then, enter “commit” for this command to take effect. Once you’ve deleted all the rows with the example name “Alan,” create your new row by entering “insert into name values (‘Alan’)” followed by "commit." When you've completed these steps, check to make sure you no longer have duplicates by entering “select * from names.” To learn how to delete multiple duplicates, keep reading!

      Did this summary help you?
      In other languages
      Thanks to all authors for creating a page that has been read 406,546 times.

      Is this article up to date?

      Advertisement