Keep one duplicate in MySQL table and delete the rest

Question

I would like to make any MySQL table entries which are not unique with respect to two columns, unique, by removing any extra entries. Lets say we have table_A below. I would like to only keep one entry where col1 and col2 are the same as those of another entry. An example result for table_A is depicted as table_B below.

table_A

id | col1 | col2 | col3
-----------------------
1  |  a   |  3   |  4 
2  |  b   |  4   |  9
3  |  c   |  3   |  4
4  |  a   |  3   |  7
5  |  d   |  5   |  8
6  |  b   |  4   |  2
7  |  a   |  3   |  1
8  |  a   |  4   |  3

table_B

id | col1 | col2 | col3
-----------------------
1  |  a   |  3   |  4 
2  |  b   |  4   |  9
3  |  c   |  3   |  4
5  |  d   |  5   |  8
8  |  a   |  4   |  3

Notice that rows

4  |  a   |  3   |  7
7  |  a   |  3   |  1
6  |  b   |  4   |  2

have been removed because a similar col1, col2 entry pair exists.

http://stackoverflow.com/questions/2630440/how-to-delete-duplicates-on-a-mysql-table?rq=1 — Lukasz Szozda, Nov 11 '15 at 16:17

score 0 · Answer 1 · answered Nov 11 '15 at 17:12

Maybe just use a join on a DELETE, checking the id on the join is larger:-

DELETE tab2
FROM table_a tab1
INNER JOIN table_a tab2
WHERE tab1.col1 = tab2.col1
AND tab1.col2 = tab2.col2
AND tab1.id < tab2.id

SQL fiddle:-

http://www.sqlfiddle.com/#!9/3b300/1

Keep one duplicate in MySQL table and delete the rest

1 Answers1