How to remove duplicate records in a mySQL table using SQL

Remove Duplicate Records in mySQL table

Some times you may come across a situation where you have duplicate records in a table. This could be due to non-usage of constraints (PRIMARY/UNIQUE indexes) at the table level or some poor logic at the code level due to which you come across this situation.

Now what ever the reason for this you need to remove the duplicate records in the table. One way would be to write a script and loop through the records and delete records based on some condition. Another quick solution would to write the following SQL statement which would delete the records in a few seconds.

DELETE tbl1 FROM [tablename] tbl1, [tablename] tbl2 WHERE tbl1.id > tbl2.id AND tbl1.sno = tbl1.sno

In the SQL above the sno column is the column where the record is duplicated for e.g. you may have 2 records which have the same stock number or model number or UPC etc. The id column is the column which you will use to determine which record you will delete from the duplicate records for e.g. this column can be an autonumber (identity) column. If your table does not have one you can create one column for this purpose and drop it later on when you have removed the duplicate records.

Please follow and like us:
error

Be the first to comment

Leave a Reply

Your email address will not be published.