delete all duplicate rows in mySQL 5.7.9

Question

I have table like this

 select id,channel,post from posts limit 10
+------+------------+-------+
| id   | channel    | post  |
+------+------------+-------+
| 1433 | channel2   | 19353 |
| 1434 | channel2   | 19353 |
| 1435 | channel2   | 19354 |
| 1436 | channel2   | 19354 |
| 1437 | channel2   | 19356 |
| 1438 | channel2   | 19357 |
| 1439 | channel2   | 19358 |
| 1440 | channel2   | 19359 |
| 1441 | channel2   | 19360 |
| 1634 | channel2   | 19360 |
+------+------------+-------+

in that table id is a primary key , now in that table i have a duplicate post for a channel, i try to add a unique key to table and remove all duplicate row with this query

ALTER ignore TABLE `posts` ADD UNIQUE key `unique_index` (`channel`, `post`);

but in mysql 5.7.9 we can not do that !

so i want to know how can i delete duplicate row and add a unique key for channel, post

---

Solution

    DELETE FROM posts
WHERE ID Not in (SELECT* 
                 FROM (SELECT MIN(ID)
                       FROM posts 
                       GROUP BY channel, Post) B
                ) 

Answer 1

You can't use the same table in a subquery within a delete. So you need to use a extra delivered table to overcome that problem.

DELETE FROM
 posts 
WHERE
 posts.id NOT IN (  
  SELECT 
   id
  FROM (    
    SELECT 
      MIN(id) AS id
    FROM
      posts 
    GROUP BY
      posts.channel
    , posts.post
  ) 
   AS 
    posts_id    
)