Everything You Need to Know About Removing Duplicate Records in Oracle

Download Article

Plus, easy steps to identify a duplicate in Oracle

Co-authored by wikiHow Staff

Last Updated: October 18, 2024 Fact Checked

Download Article

Identifying a Duplicate Record in Oracle

|

Deleting a Single Duplicate

|

Deleting Multiple Duplicates

|

Deleting Rows with Columns

|

Warnings

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

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

Section 1 of 4:

Identifying your Duplicate

Download Article

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.
In the instance of a column named "Names," you would replace "column_name" with Names.^{[1]
X
Research source}

Advertisement
3
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.^{[2]
X
Research source}
```
select column_name, count(column_name) 
from table 
group by column_name
having count (column_name) > 1;
```

Section 2 of 4:

Deleting a Single Duplicate

Download Article

After "SQL," which stands for Standard Query Language, enter "select name from names."
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."
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
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."^{[3]
X
Research source}
```
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.
```

Section 3 of 4:

Deleting Multiple Duplicates

Download Article

After "SQL," enter "select rowid, name from names;."
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]
X
Research source}
3
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.
```

Section 4 of 4:

Deleting Rows with Columns

Download Article

After "SQL," enter "select * from names;" to see your rows.
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]
X
Research source}

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.

Expert Q&A

Search

Add New Question

Ask a Question

200 characters left

Include your email address to get a message when this question is answered.

Submit

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