Loading

Remove duplicate record from a table

Anjali Khan

9y781

Hi frnds How to remove duplicate records from a table? I know from the below query but my table name is employee so y we used tempemp . Remove Duplicate Records by using ROW_NUMBER() WITH TempEmp (Name,duplicateRecCount) AS. ( SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) AS duplicateRecCount. FROM dbo. Employee. ) --Now Delete Duplicate Records

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
Accepted answer
By wrapping your query to pull record with row_number set to more than 1 ( meaning duplicates), you can delete those records.

Just select the ID in your query.
DELETE FROM dbo.Employee
WHERE ID in
(
SELECT IQ.ID
FROM (
        SELECT  ID, -- selected extra column
                Name,
                ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) AS duplicateRecCount
        FROM dbo. Employee
) AS IQ
WHERE IQ.duplicateRecCount<>1
)