MySQL: Altering a table and delete duplicate unique keys

Question

I'm trying to add a UNIQUE index to my table based on several columns. However, it is identifying duplicate entries and is erroring out. I tried using the IGNORE keyword, but it has been depreciated entirely in 5.7.

Here is my SQL:

ALTER TABLE `CotG`.`city_data` 
ADD UNIQUE INDEX `unique_index` (`date` ASC, `player` ASC, `city_id` ASC);

How can I add the unique key and have duplicate rows automatically delete?

Edit: This is not a duplicate of How to delete duplicates on a MySQL table? as the answer that addresses my question is for a MySQL features that has been depreciated.... Which was one of my issues in this question...

Create a new table with the same structure with the unique index you desire, then use `INSERT IGNORE INTO table1 (field1, field2, etc) SELECT field1, field2, etc FROM table2` if you wish to keep the most current record (latest in set) add `ON DUPLICATE KEY UPDATE`. Learn from this to always use unique index columns BEFORE you start filling with data :) — Kraang Prime, Jan 02 '17 at 04:57
Possible duplicate of [How to delete duplicates on a MySQL table?](http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table) — e4c5, Jan 02 '17 at 05:18
@KraangPrime that worked great! Please add an answer and I'll accept it. — Douglas Gaskell, Jan 02 '17 at 05:29
@e4c5 I wouldn't say there was an answer there, also considering the top answer (the one that addresses my question) is for a now deprecated MySQL feature... — Douglas Gaskell, Jan 02 '17 at 05:31
@e4c5 - good solution if you wish to delete the highest id's where name is identical - in practice it is generally better to merge the original data with the most recent data. — Kraang Prime, Jan 02 '17 at 05:37
well that's certainly not what your answer is doing at any rate @KraangPrime — e4c5, Jan 02 '17 at 05:40
@e4c5 - feel free to modify it :) .. it was really something quickly whipped together. Likely I would remove the `IGNORE` when using `ON DUPLICATE KEY`, and probably expand to set conditions for the duplicates. My solution is only a premise for merging, but it does retain the most recent record based on duplicate key if those changes are made -- the initial sample without the mentioned adjustments just discards everything else beyond the first unique entry. — Kraang Prime, Jan 02 '17 at 05:44
" it does retain the most recent record based on duplicate key" nopes it does not. or rather there is no guarantee that it will. — e4c5, Jan 02 '17 at 05:45

score 0 · Accepted Answer · answered Jan 02 '17 at 05:34

The simplest solution to this problem is to create a new table based on the previous structure, and run a query like the following :

INSERT IGNORE INTO table1
    (field1, field2, etc)
SELECT
    field1,
    field2,
FROM
    table2

If you wish to keep the most recent records (in the order stored in table1), you can add :

 ON DUPLICATE KEY UPDATE

MySQL: Altering a table and delete duplicate unique keys

1 Answers1