News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Delete duplicate rows in a table

  • Posted by Arjun Raja
  • On July 9, 2009
  • 0 Comments

In case you want to identify duplicates and remove them from a table.

1. Identify duplicates.

select count(*) from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN
(select rowid from RF_BUSINESS_UNIT_HISTORY
MINUS
select max(rowid) from RF_BUSINESS_UNIT_HISTORY
GROUP BY
CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

COUNT(*)
———-
2528

2. 2258 duplicates exist – these can be deleted with command below –

delete from RF_BUSINESS_UNIT_HISTORY WHERE ROWID IN
(select rowid from RF_BUSINESS_UNIT_HISTORY
MINUS
select max(rowid) from RF_BUSINESS_UNIT_HISTORY
GROUP BY
CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);

 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *