Krishnakant Mahamuni

• Sep 14th, 2006

 

The following query can be used to delete the duplicate rows without using rowid.

DELETE FROM TABLENAME WHERE (COLUMNS-TO-COMPARE) IN (SELECT COLUMNS-TO-COMPARE FROM TABLENAME GROUP BY COLUMNS-TO-COMPARE HAVING COUNT(*) > 1) AND ROWNUM < ( SELECT COUNT(*) FROM  TABLENAME GROUP BY COLUMNS-TO-COMPARE HAVING COUNT(*) > 1

Thanks & Regards,
Krishnakant Mahamuni.

 

Sasi

• Sep 15th, 2006

 

Can u plz explain this

bhargav

• Nov 26th, 2006

 

     To eliminate duplicate records there are Three Methods ..

1) using rowid:

        Delete from <tablename> a where rowid > (

       Select min(rowid) from <tablename> B where a.keyvalues=b.keyvalues );

                  In the above query Keyvalues means column names which we have to compare.

2) Creating another table

 create table <tablename2> as select distinct *from <tablename1>;

 drop <tablename1>

 rename <tablename2> to <tablename1>;

  Remember to recreate all indexes, constrains ,triggers ..etc on the table

3) using rowid:

  Delete from <tablename> a

  where exists(select 'x' from <tablename> b

                     where b.keyvalue1=a.keyvalue2 and

                              b.keyvalue2=b.keyvalue2 and

                              b.rowid>a.rowid );

Karthik

• Mar 13th, 2007

 

Hi,

If you have primary key Constrains in the table

Following is query will select the duplicate

----------------------------------------
Select * from X a, X b

Where a.1 != b.1 ( Primary Key)

And a 2 = b.2 (Equate All other columns)

Delete Statement:-

-----------------
Delete from X a, X b

Where a.1 <> b.1 (Primary Key Column)

And a 2 = b.2 (Equate All other columns)


Thanks
karthi

Praveen2384

 Profile Answers by Praveen2384 

• Sep 9th, 2008

 

Delete from table_name where rownum in (select min(rownum) from table_name group by column2,column3);

where column2 and column3 are repeating columns.