How to Identify and Remove the duplicate rows in a Table?

by

In case if we want to identify duplicates rows of a table and want to remove them from a table.  Below are the command which will  identify and remove the duplicare rows from a Table.

1.) Identify duplicates

1
2
3
4
5
6
SQL> select count(*) from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2
MINUS select max(rowid) from ADDRESS2 GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);
 
COUNT(*)
———-
251

Here 251 duplicates exist – these can be deleted with command below :

2.) Removing the Rows :

1
2
3
SQL> delete from ADDRESS2 WHERE ROWID IN (select rowid from ADDRESS2
MINUS select max(rowid) from ADDRESS2
GROUP BY CALENDAR_YEAR_MONTH,BUSINESS_UNIT_NO,BUSINESS_UNIT_NAME);