Delete duplicate record from Sql Server 2005

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

)

Ebook Download
View all
Learn
View all