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,'rajes h')
insert into duplicate(intId ,varName) values(2,'raj12 ')
insert into duplicate(intId ,varName) values(1,'rajes h')
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]

Easiest Way to Delete Duplicate Rows

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

http://blog.sqlauthority.com/2007/03/01/sql-server-delete-duplicate-records-rows/

Regards,