Click here to Skip to main content
15,849,907 members
Please Sign up or sign in to vote.
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
Comments
Bala Selvanayagam 14-Apr-12 14:17pm    
what version of SQL server you are using ?

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.
 
Share this answer
 
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..
 
Share this answer
 
You can use SELECT DISTINCT Empname, EmpID FROM ...
 
Share this answer
 
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

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900