Remove duplicate rows in MySQL

7 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 script 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:

TOP TUTORIALS:  Real-Time Application Dashboard with Yii2, Node.js, MySQL and Chart.js
duplicate row 01
CREATE TABLE `duplicate_row` (
  `id` int(11) DEFAULT NULL,
  `refID` int(11) DEFAULT NULL,
  `data` varchar(7) DEFAULT NULL

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');

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 script.
DELETE t1 FROM duplicate_row t1
  JOIN duplicate_row t2
  ON t2.refID = t1.refID
  1. Then click the “ Go ” button to run the script.
TOP TUTORIALS:  Create real-time applications with Node.js and MySQL
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 ” according to what you want.

Good luck…

Hope it is useful….