Deleting duplicate rows when there is no primary key

set nocount on
create table #tbFruit (Fruit varchar(20))
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘apple’)
insert into #tbFruit (Fruit) values (‘orange’)
insert into #tbFruit (Fruit) values (‘orange’)
insert into #tbFruit (Fruit) values (‘orange’)

create table #tbFruitsWithDupes (DupeID int identity, Fruit varchar(20), DupeCount int)

insert into #tbFruitsWithDupes (Fruit, DupeCount)
 select Fruit, count(*) DupeCount from #tbFruit
 group by Fruit having count(*) > 1

declare @x int, @max int, @Fruit varchar(20), @DupeCount int

select @x = 1, @max = max(DupeID) from #tbFruitsWithDupes

while (@x <= @max)
 begin
  select @Fruit = Fruit, @DupeCount = DupeCount – 1
   from #tbFruitsWithDupes Where DupeID = @x

  set rowcount @DupeCount

  delete from #tbFruit where Fruit = @Fruit

  select @x = @x + 1
 end

select * from #tbFruit

drop table #tbFruit
drop table #tbFruitsWithDupes

For Reference please see this link : http://www.xaprb.com/blog/2006/10/09/how-to-find-duplicate-rows-with-sql/

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: