#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

Possible duplicate of [MySQL Error 1093 - Can't specify target table for update in FROM clause](http://stackoverflow.com/questions/45494/mysql-error-1093-cant-specify-target-table-for-update-in-from-clause) — Pathik Vejani, Feb 09 '16 at 11:41

Abhishek Ginani · Answer 1 · 2020-01-21T12:59:29.660

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
)

edited Jan 21 '20 at 12:59

answered Feb 09 '16 at 11:57

Abhishek Ginani

4,211
2
18
33

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

1 Answers1