How to remove duplicate/repetitive rows from MS SQL Server table?


Below is the script to remove duplicate rows from MS Sql Server table:

If you have a primary key in the table (let’s call it Id), you can do something like this with a sub-query:

DELETE FROM Table_Name
WHERE Id NOT IN
(
SELECT MIN(Id)
FROM Table_Name b
WHERE
Table_Name.column1 = b.column1
AND Table_Name.column2 = b.column2
-- AND ...
)

Another way is to use GROUP BY clause within sub query:

DELETE FROM Table_Name WHERE Id NOT IN
(SELECT MAX(Id) FROM Table_Name GROUP BY Column1, Column2, Column3)

 

Comments & Responses

One Response so far.

  1. tarantej singh says:

    very helpful script. thanx

Leave a Reply

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

*


3 × 8 =

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>