#1093 - You can't specify target table 'comments' for update in FROM clause

Question

I want to delete duplicate comments from my database

Delete FROM `comments` WHERE id IN 
(
  SELECT id
  FROM comments
  GROUP BY 
  COMMENT , course_id
  HAVING COUNT( * ) >1
)

I got error #1093 -

You can't specify target table 'comments' for update in FROM clause.

how can I modify my query to work fine

Answer 1

Try aliasing the sub query:

Delete FROM `comments` WHERE id IN 
(
  select id from (
      SELECT id
      FROM comments GROUP BY  `COMMENT` , course_id
      HAVING COUNT( * ) >1
  ) as comm
)