Delete MySQL Duplicates: Step-by-Step Guide

1 year ago
Liam
1 minute

To remove duplicate data in a MySQL table, you can follow these steps:

  1. Firstly, use the SELECT statement to query duplicate data in the table and determine which fields are duplicated.
SELECT column1, column2, COUNT(*)
FROM your_table
GROUP BY column1, column2
HAVING COUNT(*) > 1;
  1. Identify the unique identifier (such as a primary key or unique index) from the search results in order to remove duplicate data.
  2. Use the DELETE statement to remove duplicate data, keeping only the first occurrence of each unique identifier.
DELETE n1
FROM your_table n1, your_table n2
WHERE n1.id > n2.id
AND n1.column1 = n2.column1
AND n1.column2 = n2.column2;

Please note that deleting data is a risky operation, it is recommended to back up the data before proceeding.

bannerAds