467,175 Members | 1,293 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Share your developer knowledge by writing an article on Bytes.

Delete Duplicate record from SQL Server2000

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...
Oct 4 '07 #1
  • viewed: 10183
Share:
3 Comments
jamesd0142
256MB
Additionally look at this post... i believe its a simpler way and works well for me!

http://www.thescripts.com/forum/thread721118.html
Oct 10 '07 #2
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.

Expand|Select|Wrap|Line Numbers
  1. Delete * from YourTable where ID <> IN ( SELECT Distinct IDColumn from YourTable)
Thanks,
Munawar
Oct 21 '07 #3
Following website demonstrates easiest way to delete duplicate records from SQL Server (any version).

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

Regards,
Dec 25 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by Charles | last post: by
6 posts views Thread by Karl Richards | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.