CODEWITHSUNDEEP
sql-server
sobsinha
sobsinha

Reputation: 111

eliminating duplicate entries

I have a Employee_Master table with columns as 

Emp_id, Emp_Name, Region_Code

Due to Data Load failure there were duplicate entries in the master table.

I found the duplicate entries are for Region_Code=5,10 & 13.

How can I find out the Duplicate Emp_id for this?

Also how can I write a query to find duplicates in a given table?

Upvotes: 0

Views: 82

Answers (1)

Akram Shahda
Akram Shahda

Reputation: 14771

The following solution works on SQL Server 2005 and later versions:

-- Find Duplicate Rows
SELECT   
   Emp_id, 
   Emp_Name, 
   MAX(Region_Code) as Region_Code
FROM 
   Employee_Master 
GROUP BY 
   Emp_id, 
   Emp_Name
HAVING 
   COUNT(*) > 1



-- Delete Duplicate Rows
DELETE FROM 
   Employee_Master 
WHERE 
   Region_Code IN 
   ( 
       SELECT 
          MAX(Region_Code) 
       FROM 
          Employee_Master 
       GROUP BY 
          Emp_id, 
          Emp_Name
       HAVING 
          COUNT(*) > 1
  )

Upvotes: 2

Related Questions