I have written a query to remove duplicate records from a table
;WITH a as
(
SELECT Firstname,ROW_NUMBER() OVER(PARTITION by Firstname, empID ORDER BY Firstname)
AS duplicateRecCount
FROM dbo.tblEmployee
)
--Now Delete Duplicate Records
DELETE FROM tblEmployee
WHERE duplicateRecCount > 1
But I don't know where I went wrong it is saying
Invalid column name
duplicateRecCount
Can someone help me?
You need to reference the CTE in the delete statement...
WITH a as
(
SELECT Firstname,ROW_NUMBER() OVER(PARTITION by Firstname, empID ORDER BY Firstname)
AS duplicateRecCount
FROM dbo.tblEmployee
)
--Now Delete Duplicate Records
DELETE FROM a
WHERE duplicateRecCount > 1
DELETE duplicates FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY firstname, lastname, EMPNO, salary,dept ORDER BY empno) cnt
FROM tblEmp) duplicates
WHERE duplicates.Cnt > 1
