Delete every 2nd duplicate row in MySQL

Asked Nov 27 '22 at 07:32

Active Nov 27 '22 at 07:53

Viewed 23 times

I've some duplicate rows in my MySQL database-

--------------------------------------------------
| id |  eiin   |  level  |   post_id  |  sub_id  |
--------------------------------------------------    
| 15 | 100003  |    2   |      5     |    301    |   //want to keep this
--------------------------------------------------    
| 65 | 100003  |    2   |      5     |    301    |   //want to delete this
--------------------------------------------------
| 40 | 100003  |    3   |      17    |    105    |   //want to keep this
--------------------------------------------------    
| 73 | 100003  |    3   |      17    |    105    |   //want to delete this
--------------------------------------------------

I would like to delete every 2nd duplicate rows as stated in the above figure.

I can't figure out the SQL. Any help?

This SO has my answer- Delete all Duplicate Rows except for One in MySQL?

edited Nov 27 '22 at 07:53

asked Nov 27 '22 at 07:32

s.k.paul

7,099
28
93
168

Isn't there a primary key in the table? – forpas Nov 27 '22 at 07:37
@forpas Yes, there is primary key. Updated table – s.k.paul Nov 27 '22 at 07:38
Can you delete any of the duplicated rows, or you must keep the first one? – Charles Han Nov 27 '22 at 07:43

Delete every 2nd duplicate row in MySQL

0 Answers0