Delete duplicate rows in MySQL

Dani 4 4K Views

Sometimes you need to delete duplicate rows in a database :)

In the future, set UNIQUE keys when you need them :)

In the example below, we have a table appropriately named TABLE_NAME and there are multiple rows that have the same value for the duplicate_field field. In this example, we want to remove the ones where the id field is set to "remove". In other examples, you can pick any conditional you want when choosing which of the rows to remove or which to keep, including conditionals between them (e.g. foo.id < bar.id will keep the row with the highest id value and delete the others).

Alternatively, if you do want to add that UNIQUE index, you can do the following to brute force dropping duplicate rows:

ALTER IGNORE TABLE TABLE_NAME
ADD UNIQUE INDEX index_name (duplicate_field);
dshort501 commented: cool +0 
DELETE foo FROM TABLE_NAME foo
INNER JOIN TABLE_NAME bar
WHERE
	foo.id = 'remove' AND
    foo.duplicate_field = bar.duplicate_field;
Member Avatar for Dani
Dani 4,329 The Queen of DaniWeb

Although I haven’t had a chance to test this for myself yet, I stumbled across someone mentioning that, as of MySQL 5.7, using the ALTER IGNORE method described above has been deprecated. You should still be able to use the INNER JOIN method, which is more robust anyways, as it gives you control over which of the duplicates to keep.

Member Avatar for stazeclop04
stazeclop04 0 Newbie Poster

Delete Duplicate Record Using Delete Join. We can use the DELETE JOIN statement in MySQL that allows us to remove duplicate records quickly. ... Delete Duplicate Record Using the ROW_NUMBER() Function. ... DELETE Duplicate Rows Using Intermediate Table.

Member Avatar for can_623
can_623 0 Newbie Poster

very good

Member Avatar for Usamatamimi
Usamatamimi 0 Newbie Poster

Display Table Data.
Delete Duplicate Rows Using Delete Join.
ContactsCopy Table Data.
Delete And Rename The Tables.
Show Row Numbers.
Delete Duplicate Rows Using Row Number Function.

Member Avatar for Usamatamimi
Usamatamimi 0 Newbie Poster

To delete duplicate rows in MySQL, you can use the following SQL query:

DELETE t1 FROM your_table t1
INNER JOIN your_table t2
WHERE t1.id < t2.id AND t1.column_name = t2.column_name;
Replace your_table with the actual name of your table and column_name with the name of the column that you want to check for duplicates based on. This query will keep only one instance of each duplicate row and delete the rest. Make sure to take a backup of your data before running delete queries to avoid accidental data loss.

Edited by Dani because: Promotional content snipped
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Edit Preview