How to Delete Duplicate Rows in Oracle and Sql Server This question is very common now a days being asked by various database administers and developers and different people give their different opinions in shape of using stored procedures, cursors and loops etc. You can do it with simpls sql query as under:- Oracle In Oracle, you can use RowID Datatype to Delete All Duplicate Rows, leaving behind one Record for each Duplicated Row. Create Table myTable (name varchar2(10), add1 varchar2(10), add2 varchar2(10)); Insert into myTable values('Jacky','Room 25','Drig Road'); Insert into myTable values('Jack2','Room 29','Drig Road'); Insert into myTable values('Jacky','Room 25','Drig Road'); Delete from myTable where rowid not in (select min(rowid) from myTable group by name,add1,add2); Sql Server As there is no such RowID type in Sql Server like Oracle, so you have to create an Identity Column with other fields as under:- Create Table myTable (myID int identity, name varchar(10), add1 varchar(10), add2 varchar(10)) go Insert into myTable values('Jacky','Room 25','Drig Road') go Insert into myTable values('Jack2','Room 29','Drig Road') go Insert into myTable values('Jacky','Room 25','Drig Road') go Delete from myTable where myID not in (select min(myID) from myTable group by name,add1,add2) go Further, if in case of sql server, you don't have identity column in your table, then you can add it without deleting the existing data with the following sql, before running the above Delete Query. alter table mytable add myid int identity Best Regards, Mohammad Luqman Usman (CEO) M/s. Pearl Soft, Karachi, Pakistan
Dear Bradmcgehee, Thanks for your Tips. But I have a question. If my Table Structure would have been created without any Identity Column then how the deletion operation on duplicate Record is possible with a single query. Thanks & Regards, Arindam Ganguly
Refer to this linkhttp://www.sql-server-performance.com/rd_delete_duplicates.asp andhttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6256 about dealing with Duplicates. HTH Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Thanks brad for your methods. Satya, your link provides lot of methods to delete duplicates. Thanks Madhivanan Failing to plan is Planning to fail
Perhaps this is one of the best methods of deleting duplicates Thanks Mr. bradmcgehee Chandra Paladugu
Yes This is a hot issue in the forum for last months There are few more articles as well www.sqlservercentral.com/columnists/ccubley/findinganddeletingduplicatedata.asp www.sqlservercentral.com/columnists/darjun/deletingduplicaterows.asp www.sqlservercentral.com/columnists/sramakrishnan/deletingduplicaterecords.asp
Sample table name – TEST Structure – as belowSNO SNAME ---- --------- 1001 ATHER 1001 ATHER 1001 ATHER 1001 ATHER 1001 ATHER Query to delete duplicate records can be written as given below SQL> delete from test a where rowid <> ( select max(rowid) from test b where a.sno = b.sno and a.sname = b.sname ) Regards, sabeer pasha.