Remove duplicate rows in MySQL

07 December 2021

Home » Blogs » Database » Remove duplicate rows in MySQL

If in your database there are repeated rows of data or duplicate rows of data occur, you can remove the duplication easily just by using a MySQL script.

In this article, we will learn to remove duplicate data rows from MySQL by using a that we will run using .

Prepare sample data table

Before we start, set up a table named ” duplicate_row ” with the following structure and data:

duplicate row 01
CREATE TABLE `duplicate_row` (
  `id` int(11) DEFAULT NULL,
  `refID` int(11) DEFAULT NULL,
  `data` varchar(7) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 PACK_KEYS=0;
INSERT INTO `duplicate_row` (`id`, `refID`, `data`) VALUES
(1, 3526, 'aaaaaaa'),
(2, 3527, 'bbbbbbb'),
(3, 3528, 'ccccccc'),
(4, 3529, 'ddddddd'),
(5, 3527, 'eeeeeee'),
(6, 3528, 'fffffff'),
(7, 3527, 'ggggggg');
COMMIT;

Remove duplicate rows using DELETE JOIN

  1. Open and select the database name from the ” duplicate_row ” table . Then click the “ SQL ” tab . Then copy the following .
DELETE t1 FROM duplicate_row t1
  JOIN duplicate_row t2
  ON t2.refID = t1.refID
  AND t2.id 
  1. Then click the “ Go ” button to run the .
duplicate row 02
  1. The script will remove all duplicate rows and keep the row with the smallest “id”.
duplicate row 04
  1. To change the deletion criteria, change the script in the “ AND section t2.id ” according to what you want.

Good luck…

Hope it is useful….

Related Articles