Tuesday 29 September 2009

Two ways of deleting duplicated rows in Oracle

The most effective way to detect duplicate rows in Oracle is to join the table against itself as shown below. Assume table_name has two columns col1 and col2

SELECT *
FROM table_name A
WHERE A.rowid > (SELECT min(B.rowid)
FROM table_name B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)

To delete the duplicate rows:

DELETE FROM table_name A
WHERE A.rowid > (SELECT min(B.rowid)
FROM table_name B
WHERE A.col1 = B.col1
AND A.col2 = B.col2)

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

delete from table_name
where rowid in (select rowid
from (select rowid,
row_number() over(partition by col1, col2 order by col1, col2) dup
from table_name)
where dup > 1);

No comments:

Post a Comment