SQL SERVER – Delete Duplicate Records – Rows

Following code is useful to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

DELETE
FROM MyTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM MyTable
GROUP BY DuplicateColumn1, DuplicateColumn2, DuplicateColumn3)

Watch the view to see the above concept in action:

Reference : Pinal Dave (https://blog.sqlauthority.com)

,
Previous Post
SQL SERVER – T-SQL Script to find the CD key from Registry
Next Post
SQL SERVER – QUOTED_IDENTIFIER ON/OFF and ANSI_NULL ON/OFF Explanation

Related Posts

450 Comments. Leave new

  • I had a scenario where I did not have any identity column, and I need to determine duplicates based on all column values. How we can do that? I thought of using Row_Number() function, but that was also not generic enough to fit into any table definition. Is there a way to do that?

    Reply
  • Thankyou . .. . always your solution is straight and simple

    Reply

Leave a Reply

Menu