Posted   by 

Oracle Sql Query To Find Duplicate Rows In A Table




  • If your source is a table, you can issue a SQL query to find the duplicate key and mark a flag. Based on the flag you can pass the data to different target in Datastage. If its Server job, you can write two different query for each target.
  • Find Duplicates From a Table in SQL Server Summary: in this tutorial, you will learn how to use the GROUP BY clause or ROWNUMBER function to find duplicate values in a table. Technically, you use the UNIQUE constraints to enforce the uniqueness of rows in one or more columns of a table.
  • Oracle tables always have one guaranteed unique column, the rowid column. If you use a min/max function against your rowid and then select against the proposed primary key you can squeeze out the rowids of the duplicate rows pretty quick.
Removing duplicate rows from Oracle tables with SQL can be very tricky, and there are several techniques for identifying and removing duplicate rows from tables:
  • Subquery to identify duplicate rows
  • Use RANK to find and remove duplicate table rows
  • Use self-join to remove duplicate rows
  • Use analytics to detect and remove duplicate rows
  • Delete duplicate table rows that contain NULL values

So First find the duplicate using above query, then delete it and deletion count should be same as row count of query above.Now run the find duplicate query again.If no duplicate then we are good for commit. Please look at below article to have deep dive in Sql. Oracle Sql tutorials: Contains the list of all the useful Oracle sql articles.


Use subquery to delete duplicate rows

Here we see an example of using SQL to delete duplicate table rows using an SQL subquery to identify duplicate rows, manually specifying the join columns:
DELETE FROM
table_name A
WHERE
a.rowid >
ANY (
SELECT
B.rowid
FROM
table_name B
WHERE
A.col1 = B.col1
AND
A.col2 = B.col2
);

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:
Rows
One of the most important features of Oracle is the ability to detect and remove duplicate rows from a table. While many Oracle DBA place primary key referential integrity constraints on a table, many shops do not use RI because they need the flexibility.


Use self-join to delete duplicate rows

The most effective way to detect duplicate rows is to join the table against itself as shown below.
select
book_unique_id,
page_seq_nbr,
image_key
from
page_image a
where
rowid >
(select min(rowid) from page_image b
where
b.key1 = a.key1
and
b.key2 = a.key2
and
b.key3 = a.key3
);

Oracle Sql Find Duplicate Rows


Please note that you must specify all of the columns that make the row a duplicate in the SQL where clause. Once you have detected the duplicate rows, you may modify the SQL statement to remove the duplicates as shown below:
delete from
table_name a
where
a.rowid >
any (select b.rowid
from
table_name b
where
a.col1 = b.col1
and
a.col2 = b.col2
)
;

Oracle Sql Query To Find Duplicate Rows In A Table Column

Use analytics to delete duplicate rows

Oracle Sql Duplicate Rows Query

You can also detect and delete duplicate rows using Oracle analytic functions:

Sql Duplicate A Row


delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);

Oracle Sql Query To Find Duplicate Rows In A Tablet


As we see, there are several ways to detect and delete duplicate rows from Oracle tables