Author: Dani

My code has a query that looks like:

INSERT INTO table
VALUES (...)
ON DUPLICATE KEY UPDATE
    id = LAST_INSERT_ID(id),
    ...

id is a PRIMARY KEY.

My question is that I can't for anything remember why I have id = LAST_INSERT_ID(id). I wrote it years ago and it's not commented. Can anyone think of a reason why I was doing this?

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);