This script will delete duplicate rows.
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
