How to remove duplicate records from a table in SQL database

Scenario 1, one record is completely identical to another

Meaning that there is no way of identifying them who is who. Usually, it happens when the table doesn’t have a unique id field due to the poor database design. Because of that, there is no way in SQL to delete one of these duplications without deleting them all first.

You can copy the whole table into a new table first,

SELECT DISTINCT * INTO newTable FROM oldTable

And then, delete the old table and move all the records back but this time make sure you have the unique identifier set up as one field.

Scenario 2, similar records

The records have one field that is the same each other and only one copy needs to be preserved. Usually, they have the unique identifier set up to identify one to another. Then, try

DELETE t1 FROM oldTable AS t1, oldTable AS t2
WHERE (t1.duplicateField = t2.duplicateField )
AND (t1.uniqueidentifier > t2.uniqueidentifier)

As a side note, if you run into a program in which you keep getting message telling you that

If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required.

that’s because one of the fields has the same name reserved as keywords in SQL server. You should put “[“ and “]” around it to tell the server that this is not a reserved keyword.

Leave a Reply

Your email address will not be published.