Duplicate Records

[ichervin]

Hi All,

How to remove the duplicate records from the table? Thanks.

[jkoopmann]

Here is an example for your enjoyment.
No warrenty attached.

SQL> create table t (col1 number);
Table created.

SQL> insert into t values (1);
1 row created.

SQL> insert into t values (2);
1 row created.

SQL> insert into t values (2);
1 row created.

SQL> insert into t values (3);
1 row created.

SQL> insert into t values (3);
1 row created.

SQL> insert into t values (3);
1 row created.

SQL> commit;
Commit complete.

/* take a look at which rows are duplicates */
SQL> select col1,count(*) from t having count(*) > 1 group by col1;

COL1 COUNT(*)
---------- ----------
2 2
3 3

/* show the rowids for these rows individually */
SQL> select rowid,col1 from t
where col1 in (select col1 from t having count(*) > 1 group by col1)
SQL> /

ROWID COL1
------------------ ----------
AAAOfLAABAAAOTyAAB 2
AAAOfLAABAAAOTyAAC 2
AAAOfLAABAAAOTyAAD 3
AAAOfLAABAAAOTyAAE 3
AAAOfLAABAAAOTyAAF 3

/* take out the MAX(rowid) and decide that these are ones you want to keep */

SQL> select max(rowid),col1 from t
where col1 in (select col1 from t having count(*) > 1 group by col1)
group by col1


MAX(ROWID) COL1
------------------ ----------
AAAOfLAABAAAOTyAAC 2
AAAOfLAABAAAOTyAAF 3

/* delete the ones you don't want to keep */
SQL> delete from t
where col1 in (select col1 from t having count(*) > 1 group by col1)
and rowid not in
(
select max(rowid) from t
where col1 in (select col1 from t having count(*) > 1 group by col1)
group by col1
);

3 rows deleted.

/* verify you still have one row for each COL1 */
SQL> select * from t;

COL1
----------
1
2
3

/* commit if you feel good about it */
SQL> commit;
Commit complete.