Delete Duplicate Rows in Oracle & Sql Server

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

Thank you. Deleting statement is really useful to me.



Best Regards,
casper

casper

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

Perhaps this is one of the best methods of deleting duplicates Thanks Mr. bradmcgehee

Chandra Paladugu

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.