Find duplicate records and delete mysql | Ram Laxman Yadav

Find duplicate records and delete mysql

However in practice we need to see that there are some records in our database they have duplicate values. This problem can be resolve by using the uniqness validations, But suppose the records are creating in a thread then it might be possible that two records will be created. This will cause the duplicate records in database which should be uniq. You can find them and delete those records by sql queries.Here is a sample example:

Find Records Having duplicate attributes:

To find the duplicate records run the following query: SELECT DISTINCT(attr_name) AS field, COUNT(attr_name) AS fieldCount FROM table_name GROUP BY attr_name HAVING fieldCount > 1.

Delete Duplicate Recods

To delete duplicate records run the following query:

DELETE FROM table_name USING table_name, table_name AS alias_table_name WHERE (table_name.id > alias_table_name.id) AND (table_name.attr_name=alias_table_name.attr_name).

Ram Laxman Yadav
Ram Laxman Yadav
Ruby on Rails Developer