How to delete those pesky duplicate SQL records…

So every once in a while I find that I have some duplicate records in my database as a result of testing or development or plain oversight. How to remedy the situation?

Well, its as easy as this simple SQL.

delete sourceTable
from YOURTABLENAME sourceTable, YOURTABLENAME compareTable
where sourceTable.fieldWithDuplicatesToMatch= compareTable.fieldWithDuplicatesToMatch
and sourceTable.PrimaryKey > compareTable.PrimaryKey

Basically, replace YOURTABLENAME with the name of your table.

Replace fieldWithDuplicatesToMatch with the field on the table that has the duplicates. Such as SKU or Email.

Replace the PrimaryKey with the table PrimaryKey or a time stamp (DateTime) field or whatever you want to use as the basis for which record to keep.

Voila! Duplicates be gone.

Leave a Reply

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