Delete Duplicate record from SQL Server2000

[rajeshkrsingh]

Hi friends,

Step1-
create table duplicate
(
intId int, varName varchar(50)
)
insert into duplicate(intId,varName) values(1,'rajesh')
insert into duplicate(intId,varName) values(2,'raj12')
insert into duplicate(intId,varName) values(1,'rajesh')
insert into duplicate(intId,varName) values(2,'raj12')
insert into duplicate(intId,varName) values(3,'raj12')

Step2-
---Create Duplicate table
create table #dupTbl
(
intId int, varName varchar(50)
)
insert into #dupTbl
select intId,varName from duplicate group by intId,varName having count(*) > 1

Step3-

delete from duplicate where intId in (select intId from #dupTbl)

Step4-

insert into duplicate
select intId,varName from #dupTbl

Finished...............
You can checked your table...

[jamesd0142]

Additionally look at this post... i believe its a simpler way and works well for me!

http://www.thescripts.com/forum/thread721118.html

[Munawar]

hi,

The above link has a good way to accoplish the task but here is another possibility but would work if there is some id column;

Delete all duplicate records ..
select distinct will bring all the uniquie records and rest would the duplicate and will be deleted.

Delete * from YourTable where ID <> IN ( SELECT Distinct IDColumn from YourTable)

Thanks,
Munawar

[pinaldave]

Following website demonstrates easiest way to delete duplicate records from SQL Server (any version).

http://blog.sqlauthority.com/2007/03...-records-rows/

Regards,