473,216 Members | 1,284 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes and contribute your articles to a community of 473,216 developers and data experts.

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
3 10894
jamesd0142
469 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
Munawar
12
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

2
by: Barbara | last post by:
Hi, I have an sql database that has the primary key set to three fields, but has not been set as unique(I didn't create the table). I have 1 record that has 2 duplicates and I am unable to delete...
2
by: ms | last post by:
Access 2000: I am trying to delete duplicate records imported to a staging table leaving one of the duplicates to be imported into the live table. A unique record is based on a composite key of 3...
1
by: Charles | last post by:
Hi MS Access & MS SQL Server Gurus ! I am trying to delete duplicate data from a LINKED SQL Server 2000 table (LinkedTable) and insert the result into a local (in Access) table. The following...
4
by: KT | last post by:
Is there any one click solution that would do the trick? I would like to create a button, so the person who maintains the database can perform clean up work to delete duplicate records which...
6
by: Karl Richards | last post by:
I am attempting to delete duplicate rows in a spreadsheet using the Excel object. Does anyone have any idea how to do this? I've looked everywhere that I can find on the Web and have not been...
2
by: mcasaurabhsumit | last post by:
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...
7
by: AccessHunter | last post by:
I am using the following code to find and delete records in a table. The logic will go through each record and if a duplicate row is found will delete it. I ran this code and it worked the first...
1
by: kimmccain | last post by:
I have a form with two buttons, Completed and Pended. When the completed button is clicked, the record is moved to the completed table and the form's record is decreased by 1. When the pended...
3
allingame
by: allingame | last post by:
Need help with append and delete duplicates I have tables namely 1)emp, 2)time and 3)payroll TABLE emp ssn text U]PK name text
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.