Download ArticleDownload ArticleThis article was co-authored by How.com.vn Staff. Our trained team of editors and researchers validate articles for accuracy and comprehensiveness. How.com.vn's Content Management Team carefully monitors the work from our editorial staff to ensure that each article is backed by trusted research and meets our high quality standards.
This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources.
This article has been viewed 398,385 times.
Learn more...
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.
Steps
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.[1]Advertisement
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]select column_name, count(column_name) from table group by column_namehaving 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."
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.
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]SQL > select name from names;NAME------------------------------AlanCarrieTomAlanrows 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------------------------------AlanCarrieTomrows 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.[4]
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 AlanAABJnsAAGAAAdfOAAB AlanAABJnsAAGAAAdfOAAC CarrieAABJnsAAGAAAdfOAAD TomAABJnsAAGAAAdfOAAF Alanrows selected.SQL > delete from names awhere rowid > (select min(rowid) from names bwhere b.name=a.name);rows deleted.SQL > select rowid,name from names;ROWID NAME------------------ ------------------------------AABJnsAAGAAAdfOAAA AlanAABJnsAAGAAAdfOAAC CarrieAABJnsAAGAAAdfOAAD Tomrows 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.[5]
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 50Carrie 51Tom 52Alan 50rows selected.SQL > delete from names awhere rowid > (select min(rowid) from names bwhere b.name=a.nameand b.age=a.age);row deleted.SQL > select * from names;NAME AGE------------------------------ ----------Alan 50Carrie 51Tom 52rows selected.SQL > commit;Commit complete.
Expert Q&A
Tips
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
References
- ↑ https://www.oracletutorial.com/advanced-oracle-sql/find-duplicate-records-oracle/
- ↑ https://www.oracletutorial.com/advanced-oracle-sql/find-duplicate-records-oracle/
- ↑ https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql
- ↑ https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql
- ↑ https://blogs.oracle.com/sql/post/how-to-find-and-delete-duplicate-rows-with-sql
About This Article
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!
Is this article up to date?
⚠️ Disclaimer:
Content from Wiki How English language website. Text is available under the Creative Commons Attribution-Share Alike License; additional terms may apply.
Wiki How does not encourage the violation of any laws, and cannot be responsible for any violations of such laws, should you link to this domain, or use, reproduce, or republish the information contained herein.
- - A few of these subjects are frequently censored by educational, governmental, corporate, parental and other filtering schemes.
- - Some articles may contain names, images, artworks or descriptions of events that some cultures restrict access to
- - Please note: Wiki How does not give you opinion about the law, or advice about medical. If you need specific advice (for example, medical, legal, financial or risk management), please seek a professional who is licensed or knowledgeable in that area.
- - Readers should not judge the importance of topics based on their coverage on Wiki How, nor think a topic is important just because it is the subject of a Wiki article.
