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.

Identifying from a column named "Names." In the instance of a column named "Names," you would replace "column_name" with Names.

select column_name, count(column_name) from table group by column_name having count (column_name) > 1; 

Select "name from names." After "SQL," which stands for Standard Query Language, enter "select name from names."

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]

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.

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. 

Select the RowID you want to delete. After "SQL," enter "select rowid, name from names;."

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]

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. 

Select your rows. After "SQL," enter "select * from names;" to see your rows.

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]

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.