How to Delete Duplicate Rows in Sql Server - CodAffection

How to Delete Duplicate Rows in Sql Server

How to remove duplicate rows in sql server

Overview

Article will cover some best practices on how to delete duplicate rows in sql server.

We’ll be using a student table. Query for the table creation

Inserted 4 rows into the table, row with roll Number 1 is repeated twice.

sql server table with duplicate rows - Dotnetmob

select * from tbl_Studentfollowing query can be used to check duplicate rows based on rollNumber,

find duplicate rows in sql server

Query result : Output shows that rollNumber with ‘1’ is repeated 2 times. 

here I will be explaining two methods for deleting duplicate rows.

Method 1 – by keeping original

In this method all duplicate rows will be deleted except the original copy. Check this query

after deletion, student table will be shown as below

delete duplicate rows in sql server but keep original

Method 2 – without keeping original

In this method all repeated rows including original copy will be deleted.

query

resulting table will be like this

Video Tutorial Available For This Topic – Watch Now!

4 thoughts on “How to Delete Duplicate Rows in Sql Server”

  1. Pingback: Eliminando filas duplicadas en MS SQL Server 2012 – Yet another web developer.

  2. What if there is an incremental date field and all other fields are duplicates after a certain date? What to do if you want to keep the earliest date as the original and delete all other duplicated fields (except the dates field that is incremented by one day each row)?

    1. WITH CTE AS
      (
      SELECT Date, Col1, Col2, Col3, ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 ORDER BY Col1, Col2, Col3) AS RN
      FROM tbl_Student
      ORDER BY Date ASC
      )

      DELETE FROM CTE WHERE RN1

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top
2 Shares
2 Shares
Share via
Copy link