Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Removing duplicates with unique index

I inserted between two tables fields A,B,C,D, believing I had created a Unique Index on A,B,C,D to prevent duplicates. However I somehow simply made a normal index on those. So duplicates got inserted. It is 20 million record table.

If I change my existing index from normal to unique or simply a add a new unique index for A,B,C,D will the duplicates be removed or will adding fail since unique records exist? I'd test it yet it is 30 mil records and I neither wish to mess the table up or duplicate it.

like image 909
user3649739 Avatar asked Apr 15 '16 12:04

user3649739


People also ask

Does unique index prevent duplicates?

You can prevent duplicate values in a field in an Access table by creating a unique index. A unique index is an index that requires that each value of the indexed field is unique.

How do I drop duplicate indexes?

Example #1: Use Index. drop_duplicates() function to drop all the occurrences of the duplicate value except the first occurrence. Output : Let's drop all occurrences of duplicate value in the Index except the first occurrence.

How do you remove duplicate lines from the file for using unique?

The uniq command is used to remove duplicate lines from a text file in Linux. By default, this command discards all but the first of adjacent repeated lines, so that no output lines are repeated. Optionally, it can instead only print duplicate lines. For uniq to work, you must first sort the output.


1 Answers

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

like image 121
Paul Spiegel Avatar answered Oct 03 '22 00:10

Paul Spiegel