This code is modified from an example I saw. The purpose is to delete duplicate records.
WITH DupRecords AS
SELECT *, ROW_NUMBER() OVER (PARTITION BY fName, EmpID, Department, City ORDER BY ID) AS RowNumber
WHERE RowNumber > 1
The table with duplicates is called dbo.DeleteDuplicateRecords. DupRecords is a CTE which is a temporary table right? So the CTE would hold the results of the SELECT statement along with RowNumber as the last column. Then the DELETE statement is applied to the CTE (temporary table) instead of the actual table with the duplicates. Why is that the case? Shouldn't the DELETE statement be applied to the actual table itself?
When I query the dbo.DeleteDuplicateRecords table, the duplicates were still removed anyway, so I don't understand how the DELETE statement can be applied to a temporary table, yet it still affects the actual table with the duplicates.