MySQL Remove Duplicate Data: Complete Guide

1 year ago
Emily Johnson
1 minute

In MySQL, there are two ways to delete duplicate data.

Method 1: Utilize the DISTINCT keyword and the INSERT INTO SELECT statement.

CREATE TABLE new_table AS
SELECT DISTINCT * FROM old_table;

DROP TABLE old_table;

ALTER TABLE new_table RENAME TO old_table;

Option 2: Utilize DELETE and temporary table

CREATE TABLE tmp_table AS
SELECT * FROM old_table GROUP BY column1, column2, ...;

DELETE FROM old_table;

INSERT INTO old_table SELECT * FROM tmp_table;

DROP TABLE tmp_table;

Both methods can be adjusted as needed to accommodate different table structures and requirements. Please choose the appropriate method based on the actual situation to delete duplicate data.

bannerAds