Delete duplicate rows in MySQL UserPageVisits:2565 active 80 80 DaniWeb 561 60 2020-01-26T10:52:12+00:00 https://www.daniweb.com/programming/databases/code/521578/delete-duplicate-rows-in-mysql

Delete duplicate rows in MySQL

Dani

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
2,565 Views
About the Author

I'm a super-geeky programmer with a passion for Internet marketing. :)

code snippet
DELETE foo FROM TABLE_NAME foo
INNER JOIN TABLE_NAME bar
WHERE
	foo.id = 'remove' AND
    foo.duplicate_field = bar.duplicate_field;
Be a part of the DaniWeb community

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