MSSQL - remove duplicate entries/users from database.

In This query , I am using Common Table Expression to delete duplicate record from table.



WITH Dublicates_CTE(UserID, Id)

AS

(

SELECT UserID, Min(BlockID) Id

FROM MyTable

GROUP BY UserID

HAVING Count(*) > 1

)

DELETE FROM MyTable

WHERE BlockID IN (

SELECT MyTable.[BlockID]

FROM MyTable

INNER JOIN Dublicates_CTE

ON MyTable.UserID = Dublicates_CTE.UserID

AND MyTable.[BlockID] <> Dublicates_CTE.Id

)