Finding Duplicates - Part II

The Next best method for deleting the duplicate is usingROWID (this is specific to Oracle DB).

Finding Duplicates using ROWID

The fastest way of detecting duplicate records in a table is to attempt to switch on a unique constraint.so lets create a Test Table and do with an example by adding some duplicate data.

SQL> create table t1 ( c1 int, c2 int, c3 char(1) );
Table created.
SQL> insert into t1 values ( 1, 50, 'a' );
1 row created.
SQL> insert into t1 values ( 1, 50, 'b' );
1 row created.
SQL> insert into t1 values ( 2, 89, 'x' );
1 row created.
SQL> insert into t1 values ( 2, 89, 'y' );
1 row created.
SQL> insert into t1 values ( 2, 89, 'z' );
1 row created.
SQL> commit;
Commit complete.

Now we will use the following SQL Statement to find the duplicatesand delete them

SQL> delete from T1

where rowid <> ( select max(rowid) from t1 b where b.c1 = t1.c1 and b.c2 = t1.c2 );

SQL> Commit;

So selecting the rows from the table shows that the duplicatesare eliminated

SQL> select * from t1;
C1 C2 C

---------- ---------- -

1 50 b

2 89 z