- Related Questions & Answers
- Selected Reading
Problem Statement:
You want to write a common procedure to find and remove duplicates from any table and columns in Oracle.
Solution:
We can use Oracle’s internal ROWID value for uniquely identifying rows in a table along with the OLAP function row_number with partition clause. The sample syntax to acheive this would like below. Belt fastened quivers xp32.
To demonstrate the usage, we will begin by creating sample data.
Use RANK to delete duplicate rows This is an example of the RANK function to identify and remove duplicate rows from Oracle tables, which deletes all duplicate rows while leaving the initial instance of the duplicate row. Once you have detected the duplicate rows, you may modify the SQL statement to remove the duplicates as shown below: delete from tablename a where a.rowid any (select b.rowid from tablename b where a.col1 = b.col1 and a.col2 = b.col2 ); Use analytics to delete duplicate rows. You can also detect and delete duplicate rows using Oracle analytic functions: delete from customer.
Example
Looking at the data we just created.
Example
| player_rank | player_name |
| 4 | ANDY MURRAY |
| 3 | NOVAK DJOKOVIC |
| 3 | NOVAK DJOKOVIC |
| 2 | RAFAEL NADAL |
| 2 | RAFAEL NADAL |
| 1 | ROGER FEDERER |
| 1 | ROGER FEDERER |
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.
Example
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.
| player_rank | player_name | rowid | rnk |
| 4 | ANDY MURRAY | AAAPHcAAAAAB/4TAAD | 1 |
| 3 | NOVAK DJOKOVIC | AAAPHcAAAAAB/4TAAC | 1 |
| 3 | NOVAK DJOKOVIC | AAAPHcAAAAAB/4TAAG | 2 |
| 2 | RAFAEL NADAL | AAAPHcAAAAAB/4TAAB | 1 |
| 2 | RAFAEL NADAL | AAAPHcAAAAAB/4TAAF | 2 |
| 1 | ROGER FEDERER | AAAPHcAAAAAB/4TAAE | 1 |
| 1 | ROGER FEDERER | AAAPHcAAAAAB/4TAAA | 2 |
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. Rosetta stone totale download. 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
Example
Output
| player_rank | player_name |
| 4 | ANDY MURRAY |
| 3 | NOVAK DJOKOVIC |
| 2 | RAFAEL NADAL |
| 1 | ROGER FEDERER |
As removing duplicates is one of the most common task a programmer performs, it would be nice to have a procedure created for reusability. Below procedure is will accept the table name from which the duplicates had to be removed along with the column names for search.
First up we will create a table type for passing dynamic number of columns to be grouped. We will then create a procedure to delete the data dynamically.
Code: Common procedure to remove duplicates
Example
Usage:
Output
| player_rank | player_name |
| 4 | ANDY MURRAY |
| 3 | NOVAK DJOKOVIC |
| 2 | RAFAEL NADAL |
| 1 | ROGER FEDERER |
How To Remove Duplicates In Oracle From Select
Last updated on JULY 08, 2020
Applies to:
Oracle iProcurement - Version 11.5.10 to 11.5.10 [Release 11.5]Information in this document applies to any platform.
Goal
Q: How to delete duplicate rows from catalog when supplier name has been merged ?
Steps to reproduce:
1. Bulkload catalog for supplier A
2. Merge supplier A into supplier B using supplier merge functionality from Core Purchasing
3. Re-run extractor
4. Search for item in procurement, still see item with old supplier name
5. Try to delete item using bulkload - get invalid supplier error
6. Bulkload item with new supplier name
7. See two search results now in procurement one for the old and one for the correct new supplier
Solution
Oracle Delete Sql
To view full details, sign in with your My Oracle Support account. |
Don't have a My Oracle Support account? Click to get started! |
| Goal |
| Solution |
| References |
How To Remove Duplicates In Oracle Sql Table
