Removing duplicates MySQL database

Question

I'm trying to remove duplicates from a MySQL table where two column values will be the same.

In this case, I want to, say, have an id column (called nid), and a hash column with the same values:

| nid |    hash    |
|  2  |   932298   |
|  2  |   932298   |

I'd like only one of them to survive, preferably the first one inserted in the database.

I'm looking at this post but my use case is slightly different:

MySQL remove duplicates from big database quick

I'm also open to other options

How is this question different from stackoverflow.com/questions/22026383/…? — Gordon Linoff
– Gordon Linoff, Commented Feb 26, 2014 at 14:19

Steven Matthews · Accepted Answer · 2014-02-26 15:39:44Z

1

ALTER IGNORE TABLE `table_name` ADD UNIQUE (`hash`)

edited Feb 26, 2014 at 15:39

Steven Matthews

11.7k50 gold badges140 silver badges259 bronze badges

answered Feb 26, 2014 at 14:56

Jerko W. Tisler

98910 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

13 Comments

Steven Matthews Over a year ago

Wouldn't hash be the primary key? There can be multiple nids, but I only want one hash value per nid (and theoretically, it should be a unique hash id /period/)

Jerko W. Tisler Over a year ago

You have to explain me this problem a little bit, do you need unique pairs or unique hashes?

Steven Matthews Over a year ago

Ok, so there will be multiple nids with the exact same value - there could be 30 different rows with an nid of 2, for example. The hash is calculated based on the properties of an object related to the database table, and should theoretically be unique - if there are two hashes that are exactly the same, that means that the two objects/rows are the same, and I only want one of them (preferably the first one inserted into the database) to remain. Is that clearer?

Jerko W. Tisler Over a year ago

can you please confirm this? You can have pairs (nid, hash) = (1,1), (1,2), (2,1), (2,2)? is that true?

Steven Matthews Over a year ago

You could theoretically have (1, 1), (1, 2), (2, 1), (2, 2), but the way the hash is calculated, it would be almost impossible to have the same hash for rows with a different nid, so it is best to assume that the only time hash will be the same is when it has the same nid. And all rows where there are duplicate hashes can be discarded.

|

avisheks · Accepted Answer · 2014-02-26 17:24:37Z

0

simplest way of achieving it I believe:

1) create table `table_copy`(id int primary key,hash varchar(255), unique(`hash`)) select distinct id,hash from `table_name`;
2) drop table `table_name`;
3) rename table `table_copy` to `table_name`;

edited Feb 26, 2014 at 17:24

answered Feb 26, 2014 at 14:23

avisheks

1,1801 gold badge11 silver badges28 bronze badges

2 Comments

Steven Matthews Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''table_copy'(nid,hash) as select distinct nid,hash from node_revision' at line 1

avisheks Over a year ago

@AndrewAlexander, edits being made try now..syntax were not exact but anyway you get the idea yeah?

Collectives™ on Stack Overflow

Removing duplicates MySQL database

2 Answers 2

13 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

13 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related