Delete if Duplicate Rows exist in Table

 

The following statement uses a common table expression (CTE) to delete duplicate rows: 

WITH cte AS (

    SELECT 

   SalaryYear, SalaryMonth, SalaryDate, OfficeID, EmployeeID,

   PRComponentID, PRTranTypeID,

   IsPosted, IsActive,

        ROW_NUMBER() OVER (

            PARTITION BY 

                SalaryYear, SalaryMonth, SalaryDate, OfficeID, EmployeeID,

   PRComponentID, PRTranTypeID,

   IsPosted, IsActive

            ORDER BY 

                SalaryYear, SalaryMonth, SalaryDate, PRSalaryConfigurationID, OfficeID, EmployeeID,

   PRComponentID, ComponentAmount, PRTranTypeID,

   IsPosted, IsActive

        ) row_num

     FROM 

        PRSalaryRegister

WHERE 

   SalaryMonth = 5 AND

    SalaryYear = 2020 AND 

EmployeeId = 46061

)

DELETE FROM cte

WHERE row_num > 1;

--SELECT * INTO PRSalaryRegister_8_February_2022 FROM PRSalaryRegister