How to Delete Duplicate Record in sql server 2000

[mcasaurabhsumit]

Hello Friend, I am sumit saurabh. I am doing MCA from IGNOU.
Can you plz. Help me to write a query-------
How to delete duplicate Records from a table using single statement.
Note: one Record should not be deleted out of all duplicated record.

[iburyak]

Unfortunately I don't think there is a way to delete it with a single SQL statement.

The only way I know how to is below:

[PHP]set rowcount 1

select * from table_name
while @@Rowcount > 0
Delete from table_name where id in (select distinct id from table_name group by id having count(*) > 1)

set rowcount 0[/PHP]

Make sure id is a primary key on your table.

Test it first to make sure it worked.


Good Luck.

[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...

Regards,
Rajesh Kumar Singh