SQL Server - Convert a SELECT statement into a DELETE statement-CodePudding

The below select statement selects ALL records that have duplicates (including the original).

How do I convert the below SELECT statement to a DELETE or DELETE-WHERE statement?

select *
  from Csv a
  join ( select EmployeeId, DivisionId 
          from Csv 
          group by EmployeeId, DivisionId 
          having count(*) > 1 ) b
    on a.EmployeeId = b.EmployeeId
   and a.DivisionId = b.DivisionId;

The language is SQL Server/MS-SQL

Note: There is no ID column.

CodePudding user response：

Deleting all the doubles based on the original select

delete a
  from Csv a
  join ( select EmployeeId, DivisionId 
          from Csv 
          group by EmployeeId, DivisionId 
          having count(*) > 1 ) b
    on a.EmployeeId = b.EmployeeId
   and a.DivisionId = b.DivisionId;

CodePudding user response：

Use COUNT(*) window function in an updatable CTE:

WITH cte AS (
  SELECT *, COUNT(*) OVER (PARTITION BY EmployeeId, DivisionId) counter 
  FROM csv
)
DELETE FROM cte
WHERE counter > 1;

See a simplified demo.