How to Delete Duplicate Records in Oracle?

How to Delete Duplicate Records in Oracle

How to Delete Duplicate Rows in SQL

If you work within Oracle when working in Oracle, you might find that your records contain duplicates. You can eliminate duplicate records by finding them by using the RowID or row address. Before creating a backup table, you have to refer to them once you’ve deleted the records.

Identifying the duplicate

In this instance, you must identify the duplicate in the example, “Alan.” Check to ensure that the records you’re trying to eliminate are duplicates by typing the SQL below.

The column you are identifying from is named “Names.” In the case of a column called “Names,” you would replace “column_name” with Names.

Identification of the duplicate from different columns. If you wanted to find the duplicate using other columns like Alan’s age Alan instead of his name, you could input “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;

Deleting a Single Duplicate

Choose “name from names.” After “SQL,” which stands for Standard Query Language, enter “select name from names.”

Remove all rows that have duplicate names. After “SQL,” enter “delete from names with name=’Alan’.” ‘;.” It is essential to capitalize in this case, and it will erase all rows named “Alan.” After “SQL,” enter “commit in the next column.”

Rent the row without duplicate. Now that you have eliminated all rows using the names “Alan,” you can add one back in by typing “insert into names numbers (‘Alan ‘);.” Then, after “SQL,” enter “commit” to create a new row.

Look over your list of new items. Once you’ve completed the steps above After that, you should ensure that you don’t have duplicate records. You can do this 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.

Please Click here for Perfect Understanding: https://www.wikihow.com/Delete-Duplicate-Records-in-Oracle

 

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.