How to Delete Duplicate Records From table ?

Question

0.00/5 (No votes)

See more:

Hi Folks i have table --> Empdetails

Empname EmpID
------- ------
AAA 1001
AAA 1001
BBB 1002
BBB 1002

i want to delete duplicate records not both the records.

the result should be like

Empname EmpID
------- -----
AAA 1001
BBB 1002

Please help me...!!!!

Posted 14-Apr-12 2:51am

Prasad Guduri

Add a Solution

Comments

Bala Selvanayagam 14-Apr-12 14:17pm

what version of SQL server you are using ?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jorge J. Martins · Answer 1 · 2012-04-14T03:50:00

Solution 2

If the table doesn't have an ID column I would do that SELECT DISTINCT into a new table, delete all records on the Empdetails table and repopulate that table with all the records from that initial temp table.

Posted 14-Apr-12 3:50am

Jorge J. Martins

Comments

Prasad Guduri 14-Apr-12 10:25am

if suppose record will be like

Uniqueid empid empname
-------- ----- -------
1 1001 prasad
2 1001 prasad
3 1002 arun
4 1002 arun

then how to remove duplicates ?

Jorge J. Martins 14-Apr-12 10:42am

You could try:

DELETE FROM Empdetails
FROM Empdetails INNER JOIN Empdetails AS Empdetails_1 ON Empdetails.ID < Empdetails_1.ID AND Empdetails.EmpID = Empdetails_1.EmpID

AshishChaudha 29-Jun-12 4:51am

good answer..

dharan1990 · Answer 2 · 2012-06-28T21:30:00

Solution 3

http://support.microsoft.com/kb/139444[^]

Posted 28-Jun-12 21:30pm

dharan1990

Jorge J. Martins · Answer 3 · 2012-04-14T03:33:00

Solution 1

You can use SELECT DISTINCT Empname, EmpID FROM ...

Posted 14-Apr-12 3:33am

Jorge J. Martins

Comments

Prasad Guduri 14-Apr-12 9:35am

but my requirement is delete... not display...!!!!

Jorge J. Martins 14-Apr-12 9:37am

I'm sorry!
Does your table has an ID column?

Prasad Guduri 14-Apr-12 9:52am

Yes there is a EmpID column in my table.

Jorge J. Martins 14-Apr-12 9:56am

I mean a unique ID for every record.

Argonia 17-Apr-12 3:17am

you should set the property unique for your EmpID