A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

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

1 min read

asrblogger

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

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 :

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);