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);
1,909 Views
DELETE foo FROM TABLE_NAME foo
INNER JOIN TABLE_NAME bar
WHERE
	foo.id = 'remove' AND
    foo.duplicate_field = bar.duplicate_field;
About the Author
Member Avatar Dani The Queen of DaniWeb

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