- Trending Categories
- Data Structure
- Operating System
- C Programming
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How to find and remove duplicates from a table in Oracle?
You want to find and remove duplicates from a table in Oracle.
Solution: We can use Oracle’s internal ROWID value for uniquely identifying rows in a table. The sample syntax to acheive this would like below.
delete from table where rowid in (... query here ...)
To demonstrate the usage, we will begin by creating sample data.
-- table with tennis player rankings DROP TABLE atp_stats; CREATE TABLE atp_stats ( player_rank NUMBER NOT NULL, player_name VARCHAR2(100) NOT NULL, time_range TIMESTAMP(6)); -- sample records INSERT INTO atp_stats VALUES (1,'ROGER FEDERER',CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (2,'RAFAEL NADAL',CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (3,'NOVAK DJOKOVIC',CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (4,'ANDY MURRAY',CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (1,'ROGER FEDERER',CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (2,'RAFAEL NADAL',CURRENT_TIMESTAMP); INSERT INTO atp_stats VALUES (3,'NOVAK DJOKOVIC',CURRENT_TIMESTAMP); COMMIT;
Looking at the data we just created.
SELECT * FROM atp_stats ORDER BY 2;
So, we have inserted 3 duplciates which we wanted to remove. before we go on and write a Delete statement, let us understand the inner query with ROWID.
SELECT rowid FROM ( SELECT player_rank, player_rank, rowid , row_number() over (partition BY player_rank, player_name order by player_rank,player_name) AS rnk FROM atp_stats ) WHERE rnk > 1;
I had intentionally added the columns player_rank and player_name to this innermost subquery to make the logic understandable. Ideally, innermost subquery could be written without them to the same effect. If we execute just this innermost query offcourse with the extra columns selected for clarity, we see these results.
The SQL returns the rowid for all the rows in the table. The ROW_NUMBER() function then works over sets of id and player_name driven by the PARTITION BY instruction. This means that for every unique player_rank and player_name, ROW_NUMBER will start a running count of rows we have aliased as rnk. When a new player_rank and player_name combination is observed, the rnk counter resets to 1.
Now we can apply the DELETE operator to remove the duplicate values as below.
SQL: Remove duplicates
DELETE FROM atp_stats WHERE rowid IN ( SELECT rowid FROM( SELECT player_rank, player_name, rowid , row_number() over (partition BY player_rank, player_name order by player_rank,player_name) AS rnk FROM atp_stats ) WHERE rnk > 1 );
3 rows deleted.
- How to write a common procedure to find and remove duplicates from any table and columns in Oracle?
- How to remove a record from an existing table in oracle database using JDBC API?
- How to remove duplicates from MongoDB Collection?
- How to remove duplicates from an ArrayList in Java?
- How to remove duplicates from a sorted linked list in android?
- Remove duplicates from a List in C#
- How do you remove duplicates from a list in Python?
- Python - Ways to remove duplicates from list
- Python program to remove Duplicates elements from a List?
- Java program to remove duplicates elements from a List
- How to drop a table from Oracle database using JDBC API?
- How to delete all records from a table in Oracle using JDBC API?
- Remove all duplicates from a given string in Python
- Remove all duplicates from a given string in C#
- Remove Duplicates from Sorted Array in Python