Delete duplicates from SQL Server table

romsom
romsom used Ask the Experts™
on
Hi Experts,
I'd like to delete duplicates from a SQL Server table. This is the query to show the duplicates:

SELECT Emp_Id, COUNT(Emp_Id) FROM TrainingLocal Where CourseId='164' GROUP BY Emp_Id HAVING COUNT(Emp_Id)>1

Would you please help me write a query that deletes the duplicates from this table?
I would really appreciate your help
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
;WITH cte_dups AS (
    SELECT Emp_Id, ROW_NUMBER() OVER(PARTITION BY Emp_Id ORDER BY (SELECT NULL)) AS row_num
    FROM TrainingLocal
    WHERE CourseId='164'
)
DELETE FROM cte_dups
WHERE row_num > 1
romsomIT Developer

Author

Commented:
Thank you so much, this is perfect!
Doug BishopDatabase Developer
Commented:
; WITH cteDups AS (
SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY BY Emp_Id) as RowNum,
               Emp_Id
FROM TrainingLocal 
Where CourseId='164')

DELETE cteDups WHERE RowNum > 1

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014

Commented:
You're welcome!
With CTE_Duplicates as
   (select Emp_Id, row_number() over(partition by empid order by empid) rownumber 
   from TrainingLocal Where CourseId='164' )
   delete from CTE_Duplicates where rownumber!=1
   

Open in new window


or
DELETE DUP
FROM
(
 SELECT ROW_NUMBER() OVER (PARTITION BY Emp_Id ORDER BY Emp_Id) AS Val
 FROM TrainingLocal Where CourseId='164' 
) DUP
WHERE DUP.Val > 1

Open in new window

romsomIT Developer

Author

Commented:
Thank you everyone for your quick and helpful responses!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial