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.
X
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
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/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”,”smallWidth”:460,”smallHeight”:348,” bigWidth”:728,”bigHeight”:551,”licensing”:”<div class=”mw-parser-output”></div>”}
Identify duplicate data. In this example, we will identify duplicate data as the name “Alan”. You need to make sure that the data that needs to be removed is indeed duplicated by entering the SQL (Standard Query Language) structured query language below.
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/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”,”smallWidth”:460,”smallHeight”:348,” bigWidth”:728,”bigHeight”:551,”licensing”:”<div class=”mw-parser-output”></div>”}
Look in the column named “Names”. In the example of the column named “Names,” you would replace “cpumn_name” with Names.
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/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”,”smallWidth”:460,”smallHeight”:348,” bigWidth”:728,”bigHeight”:551,”licensing”:”<div class=”mw-parser-output”></div>”}
Search in another column. If you want to identify duplicate data in another column, such as Alan’s age instead of his name, enter “Ages” in the “cpumn_name” place, otherwise the same.
select cpumn_name , count ( cpumn_name ) from table group by cpumn_name having count ( cpumn_name ) > 1 ;
Delete a duplicate data
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/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”,”smallWidth”:460,”smallHeight”:348,” bigWidth”:728,”bigHeight”:551,”licensing”:”<div class=”mw-parser-output”></div>”}
Select “name from names”. After “SQL,” enter “select name from names”.
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/14/Delete-Duplicate-Records-in-Oracle-Step-5.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-5.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Remove all lines with duplicate names. After “SQL,” enter “delete from names where name=’Alan’;”. Note that capitalization is important as this will remove all lines with the name “Alan.” After “SQL,” enter “commit.” [1] X Research Source
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/4/4c/Delete-Duplicate-Records-in-Oracle-Step-6.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-6.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Re-enter the line with no duplicate data. Now after you’ve deleted all lines named “Alan,” you can re-insert a name by typing “insert into name values (‘Alan’);”. After “SQL,” type “commit” to create a new line.
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/84/Delete-Duplicate-Records-in-Oracle-Step-7.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-7.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
See the new listing. After completing the above steps, you can check to make sure there are no duplicates by typing “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 .
Delete lots of duplicate data
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/6/64/Delete-Duplicate-Records-in-Oracle-Step-8.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-8.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Select the RowID that you want to delete. After “SQL,” you enter “select rowid, name from names;”.
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/1/17/Delete-Duplicate-Records-in-Oracle-Step-9.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-9.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Delete duplicate data. After “SQL,” enter “delete from names a where rowid > (select min(rowid) from names b where b.name=a.name);” to remove duplicate data. [2] X Research Source
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/d/d2/Delete-Duplicate-Records-in-Oracle-Step-10.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-10.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Check for duplicate data. After completing the above step, check if there are any matching data by entering the command “select rowid,name from names;” and “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 .
Delete row with column
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/8/88/Delete-Duplicate-Records-in-Oracle-Step-11.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-11.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Select line. After “SQL,” you type “select * from names;” to see the line you need.
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/7/7a/Delete-Duplicate-Records-in-Oracle-Step-12.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-12.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Delete identical rows by specifying their columns. 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 remove duplicate data. [3] X Research Sources
{“smallUrl”:”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”,”bigUrl”:”https://www.wikihow.com/images/thumb/3/31/Delete-Duplicate-Records-in-Oracle-Step-13.jpg/ v4-728px-Delete-Duplicate-Records-in-Oracle-Step-13.jpg”,”smallWidth”:460,”smallHeight”:348,”bigWidth”:728,”bigHeight”:551,”licensing”:” <div class=”mw-parser-output”></div>”}
Check for duplicate data. After completing the above steps, enter “select * from names;” and “commit” to check if you have removed all duplicate data.
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 .
X
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: