Delete Duplicate Rows

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

Cascade Deletes

I’ve rarely seen cascade deletes used by SQL Server developers, but they can be handy in the right situations. If you are unaware of what cascade deletes are, they are a way of ensuring deletes don’t leave you with orphaned records. For instance, without cascade deletes, it’s up to the developer(s) to ensure that the corresponding phone records are deleted when a person is deleted from the database. Cascading deletes however, automatically delete the children (phone) when the parent (person) is deleted.

Below is an example of how to do them.

drop table tbPerson
drop table tbPhone

create table tbPerson (	PersonID int identity,
FirstName varchar(50) not null,
constraint PK_tbPerson primary key clustered(PersonID))

create table tbPhone (	PhoneID int identity,
PersonID int not null,
PhoneNum char(10) not null,
constraint PK_tbPhone primary key clustered (PhoneID),
constraint FK_tbPhone_tbPerson foreign key (PersonID) references tbPerson (PersonID)
on delete cascade
on update cascade
)

insert into tbPerson (FirstName) values ('Bob')
insert into tbPhone (PersonID, PhoneNum) values (1, '2177266291')

select * from tbPerson
select * from tbPhone

delete from tbPerson where FirstName = 'Bob'

select * from tbPerson
select * from tbPhone