You are viewing the article How to Remove Duplicate Data in Oracle at Tnhelearning.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
select cpumn_name , count ( cpumn_name ) from table group by cpumn_name having count ( cpumn_name ) > 1 ;
Delete a duplicate data
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 .
Delete lots of duplicate data
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 .
Delete row with column
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 .
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 > create table alan . names_backup as select * from names ; Table created .
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 Tnhelearning.edu.vn You can comment, see more related articles below and hope to help you with interesting information.
Related Search: