CODEWITHSUNDEEP
mysql
Ascelhem
Ascelhem

Reputation: 423

Remove duplicated entries mysql

So, I have a small table that has some duplicated entries that I want to delete. There are two situation:

 1  2
+--+-+
|A |1|
|B |1|
|A |2|
|C |1|
+--+-+

This table is absolutely correct even if there is a duplicated record in first column. And it it the bad result that I want to avoid. 

 1  2
+--+-+
|A |1|
|B |3|
|A |1|
|C |2|
+--+-+

As you can see, there are two A with value 1 that is duplicated entire and I want to remove it. And the correct table would be like that

  1  2
+--+-+
|A |1|->remain
|B |3|
|A |1|->delete
|C |2|
+--+-+

So is there any proper way that allows me to do it via mysql? Or should I write a script on %language_name% to do this task?

Upvotes: 1

Views: 36

Answers (1)

user2314737
user2314737

Reputation: 29407

You can add a unique index to your table:

ALTER IGNORE TABLE MyTable
ADD UNIQUE INDEX idx_name (col1,col2);

where MyTable is the name of your table, col1 and col2 the names of yourcolumns 1 and 2.

Demo: http://sqlfiddle.com/#!9/f176ba/2

Upvotes: 2

Related Questions