How to delete duplicate rows in a MySQL Database

In this tutorial I am sharing the code how to delete duplicate entries from MySQL database based on a field_name.

Below SQL script creating table in a MYSQL database:

  CREATE TABLE IF NOT EXISTS `product` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `item_code` varchar(255) NOT NULL,
  `cat_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

Insert values in MYSQL database:

  INSERT INTO `product` (`name`, `item_code`, `cat_id`) VALUES ('Product-1', '0011', 7);
  INSERT INTO `product` (`name`, `item_code`, `cat_id`) VALUES ('Product-2', '0012', 2);
  INSERT INTO `product` (`name`, `item_code`, `cat_id`) VALUES ('Product-3', '0013', 1);
  // duplicate rows
  INSERT INTO `product` (`name`, `item_code`, `cat_id`) VALUES ('Product-4', '0015', 4);
  INSERT INTO `product` (`name`, `item_code`, `cat_id`) VALUES ('Product-5', '0015', 4);
  INSERT INTO `product` (`name`, `item_code`, `cat_id`) VALUES ('Product-6', '0015', 4);

Solution:

If you want to keep the row with the highest id value:

  DELETE p1 FROM product as p1, product as p2 WHERE p1.id < p2.id AND p1.item_code = p2.item_code

If you want to keep the row with the lowest id value:

  DELETE p1 FROM product as p1, product as p2 WHERE p1.id > p2.id AND p1.item_code = p2.item_code