SQL - Delete Duplicate Rows

CODING

Working with data in databases could be very intensive and complex especially if you do not have experience and routine. 

We still grow our knowledgebase with real-life examples that you could use. One of our favorite SQL tips and tricks is how to delete duplicate records in SQL.

- Advertisement -

Here is the syntax of query that will delete all duplicated rows except the last one.

-- SQL command deletes duplicate records
DELETE
FROM [table_name]
WHERE ID NOT IN (
  SELECT MAX([id_column])
  FROM [table_name]
  GROUP BY id_column
)

For example, if we want to delete duplicates in table Students with ID as an identity column we'll use the following query:

DELETE
FROM Students
WHERE ID NOT IN (
  SELECT MAX(ID)
  FROM Students
  GROUP BY ID
)

- Advertisement -