You are viewing the article How to Remove Duplicate Data in Oracle at Thptlaihoa.edu.vn you can quickly access the necessary information in the table of contents of the article below.
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 4,919 times.
When working in Oracle, sometimes you will come across some duplicate data. You can remove these duplicate rows by identifying and using their RowID or line address. Before you begin, you should back up your database in case you need to revisit it later.
Steps
Identify duplicate data
![Image titled Delete Duplicate Records in Oracle Step 1](https://www.wikihow.com/images_en/thumb/7/7f/Delete-Duplicate-Records-in-Oracle-Step-1-Version-2.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-1-Version-2.jpg)
![Image titled Delete Duplicate Records in Oracle Step 2](https://www.wikihow.com/images_en/thumb/c/c9/Delete-Duplicate-Records-in-Oracle-Step-2-Version-2.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-2-Version-2.jpg)
![Image titled Delete Duplicate Records in Oracle Step 3](https://www.wikihow.com/images_en/thumb/b/be/Delete-Duplicate-Records-in-Oracle-Step-3-Version-2.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-3-Version-2.jpg)
selectcpumn_name,count(cpumn_name)fromtablegroupbycpumn_namehavingcount(cpumn_name)>1;
Delete a duplicate data
![Image titled Delete Duplicate Records in Oracle Step 4](https://www.wikihow.com/images_en/thumb/e/e6/Delete-Duplicate-Records-in-Oracle-Step-4-Version-2.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-4-Version-2.jpg)
![Image titled Delete Duplicate Records in Oracle Step 5](https://www.wikihow.com/images_en/thumb/1/14/Delete-Duplicate-Records-in-Oracle-Step-5.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-5.jpg)
![Image titled Delete Duplicate Records in Oracle Step 6](https://www.wikihow.com/images_en/thumb/4/4c/Delete-Duplicate-Records-in-Oracle-Step-6.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-6.jpg)
![Image titled Delete Duplicate Records in Oracle Step 7](https://www.wikihow.com/images_en/thumb/8/84/Delete-Duplicate-Records-in-Oracle-Step-7.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-7.jpg)
SQL>selectnamefromnames;NAME------------------------------AlanCarrieTomAlanrowsselected.SQL>deletefromnameswherename='Alan';rowsdeleted.SQL>commit;Commitcomplete.SQL>insertintonamesvalues('Alan');rowcreated.SQL>commit;Commitcomplete.SQL>select*fromnames;NAME------------------------------AlanCarrieTomrowsselected.
Delete lots of duplicate data
![Image titled Delete Duplicate Records in Oracle Step 8](https://www.wikihow.com/images_en/thumb/6/64/Delete-Duplicate-Records-in-Oracle-Step-8.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-8.jpg)
![Image titled Delete Duplicate Records in Oracle Step 9](https://www.wikihow.com/images_en/thumb/1/17/Delete-Duplicate-Records-in-Oracle-Step-9.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-9.jpg)
![Image titled Delete Duplicate Records in Oracle Step 10](https://www.wikihow.com/images_en/thumb/d/d2/Delete-Duplicate-Records-in-Oracle-Step-10.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-10.jpg)
SQL>selectrowid,namefromnames;ROWIDNAME------------------- ---------------------------------------AABJnsAAGAAAdfOAAAAlanAABJnsAAGAAAdfOAABAlanAABJnsAAGAAAdfOAACCarrieAABJnsAAGAAAdfOAADTomAABJnsAAGAAAdfOAAFAlanrowsselected.SQL>deletefromnamesawhererowid>(selectmin(rowid)fromnamesbwhereb.name=a.name);rowsdeleted.SQL>selectrowid,namefromnames;ROWIDNAME------------------- ---------------------------------------AABJnsAAGAAAdfOAAAAlanAABJnsAAGAAAdfOAACCarrieAABJnsAAGAAAdfOAADTomrowsselected.SQL>commit;Commitcomplete.
Delete row with column
![Image titled Delete Duplicate Records in Oracle Step 11](https://www.wikihow.com/images_en/thumb/8/88/Delete-Duplicate-Records-in-Oracle-Step-11.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-11.jpg)
![Image titled Delete Duplicate Records in Oracle Step 12](https://www.wikihow.com/images_en/thumb/7/7a/Delete-Duplicate-Records-in-Oracle-Step-12.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-12.jpg)
![Image titled Delete Duplicate Records in Oracle Step 13](https://www.wikihow.com/images_en/thumb/3/31/Delete-Duplicate-Records-in-Oracle-Step-13.jpg/v4-728px-Delete-Duplicate-Records-in-Oracle-Step-13.jpg)
SQL>select*fromnames;NAMEAGE------------------------------ ----------Alan50Carrie51Tom52Alan50rowsselected.SQL>deletefromnamesawhererowid>(selectmin(rowid)fromnamesbwhereb.name=a.nameandb.age=a.age);rowdeleted.SQL>select*fromnames;NAMEAGE------------------------------ ----------Alan50Carrie51Tom52rowsselected.SQL>commit;Commitcomplete.
Warning
- Before deleting any data, back up the database during the login session so you can review it (in case you wonder about something).
SQL>createtablealan.names_backupasselect*fromnames;Tablecreated.
This article is co-authored by a team of editors and trained researchers who confirm the accuracy and completeness of the article.
The wikiHow Content Management team carefully monitors the work of editors to ensure that every article is up to a high standard of quality.
This article has been viewed 4,919 times.
When working in Oracle, sometimes you will come across some duplicate data. You can remove these duplicate rows by identifying and using their RowID or line address. Before you begin, you should back up your database in case you need to revisit it later.
Thank you for reading this post How to Remove Duplicate Data in Oracle at Thptlaihoa.edu.vn You can comment, see more related articles below and hope to help you with interesting information.
Related Search: