Homefeatured

SQLServer Delete Duplicate Rows

Like Tweet Pin it Share Share Email

Deleting duplicate rows in SQLServer is very simple.

Example : Check this table let me call it as Sample, order number 1,2 & 3  have identical values. Now we need to delete the duplicate rows.

Duplicate-01

 

Step 1 : Insert another column in table Sample called  Dup of type Integer.

Step 2 : Execute this Query  (uses Common Table Expression)

WITH DUP_CTE AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ORDERNO,Name,COST ORDER BY ORDERNO) AS DupRank FROM Sample
)

UPDATE DUP_CTE  SET Dup = DupRank

Now the table looks like this

Deuplicate-02

 

Step 3 : Delete all the rows where Dup greater than 1

DELETE Sample Where Dup > 1

Duplicate-03

 

Step 4 : If  new column Dup is not needed drop it from Table.

Easy right !!

Comments (0)

Leave a Reply

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