Removing duplicates with unique index

by

If you have duplicates in your table and you use

ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);

the query will fail with Error 1062 (duplicate key).

But if you use IGNORE

-- (only works before MySQL 5.7.4)
ALTER IGNORE TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);

the duplicates will be removed. But the documentation doesn’t specify which row will be kept:

  • IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or
    if warnings occur when strict mode is enabled. If IGNORE is not
    specified, the copy is aborted and rolled back if duplicate-key errors
    occur. If IGNORE is specified, only one row is used of rows with
    duplicates on a unique key. The other conflicting rows are deleted.
    Incorrect values are truncated to the closest matching acceptable
    value.

    As of MySQL 5.7.4, the IGNORE clause for ALTER TABLE is removed and
    its use produces an error.

(ALTER TABLE Syntax)

If your version is 5.7.4 or greater – you can:

  • Copy the data into a temporary table (it doesn’t technically need to be temporary).
  • Truncate the original table.
  • Create the UNIQUE INDEX.
  • And copy the data back with INSERT IGNORE (which is still available).
CREATE TABLE tmp_data SELECT * FROM mytable;
TRUNCATE TABLE mytable;
ALTER TABLE mytable ADD UNIQUE INDEX myindex (A, B, C, D);
INSERT IGNORE INTO mytable SELECT * from tmp_data;
DROP TABLE tmp_data;

If you use the IGNORE modifier, errors that occur while executing the
INSERT statement are ignored. For example, without IGNORE, a row that
duplicates an existing UNIQUE index or PRIMARY KEY value in the table
causes a duplicate-key error and the statement is aborted. With
IGNORE, the row is discarded and no error occurs. Ignored errors
generate warnings instead.

(INSERT Syntax)

Also see: INSERT … SELECT Syntax and Comparison of the IGNORE Keyword and Strict SQL Mode

More Related Contents:

  1. How do I delete all the duplicate records in a MySQL table without temp tables
  2. Remove duplicate rows in MySQL
  3. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  4. Find duplicate records in MySQL
  5. How to delete duplicates on a MySQL table?
  6. How to abort INSERT operation in MySql trigger?
  7. Prevent auto increment on MySQL duplicate insert
  8. Deleting duplicate rows from a table
  9. In MySQL, can I copy one row to insert into the same table?
  10. How to delete duplicate rows from a MySQL table
  11. MySQL delete duplicate records but keep latest
  12. How to remove duplicate entries from a mysql db?
  13. Count duplicates records in Mysql table?
  14. How to do unique constraint works with NULL value in MySQL
  15. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?
  16. how to delete data from table A besides the customer service and director data in table B?
  17. How to properly create composite primary keys – MYSQL
  18. MySQL: multiple tables or one table with many columns? [closed]
  19. How can I Insert many rows into a MySQL table and return the new IDs?
  20. What is cardinality in MySQL?
  21. How to prepend a string to a column value in MySQL?
  22. How to get first day of every corresponding month in mysql?
  23. MySql: Tinyint (2) vs tinyint(1) – what is the difference?
  24. MySQL Enum performance advantage?
  25. Dynamic table name at sql statement
  26. MySQL Inner Join Query Multiple Tables
  27. Select all months within given date span, including the ones with 0 values
  28. Use Composite Primary Key as Foreign Key
  29. List of Constraints from MySQL Database
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment