---

Search posts under SQL Server:

---

Delete duplicate rows in SQL Server

How to delete duplicate rows from the database table in SQL Server?

To delete duplicate rows from SQL Server database tables, we can use below SQL Statements. In this case we are creating a CTE (Common Table Expression - explained in following points) with ROW_NUMBER based on the value of FullName and City from MyDetails table. The duplicate of FullName and City count will be stored into RecordCount column (dynamic column). After that we are deleting the record from the CTE whose RecordCouint is greater than 1 (ie. Duplicate rows
found).

  WITH PersonalDetailsCTE AS
       (
              SELECT ROW_NUMBER() OVER (PARTITION BY FullName, City ORDER BY
AutoId) AS RecordCount FROM MyDetails
       )
    DELETE FROM PersonalDetailsCTE WHERE RecordCount > 1

Running the above code blocks shows the number rows affected, ie. Deleted.