3

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

davejal
  • 6,009
  • 10
  • 39
  • 82
Deed kiven
  • 37
  • 2

1 Answers1

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
)
Abhishek Ginani
  • 4,511
  • 4
  • 23
  • 35