CODEWITHSUNDEEP
mysql
TKVideoChat
TKVideoChat

Reputation: 61

MySQL: How to make select and delete in the same table in single query?

How to make select and delete in the same table in single query?

When I try:

DELETE
FROM comment
WHERE comment_id =
(select comment_id from comment where parent_id = 0 group by autor_id, user_id, resource_id, comment_body HAVING COUNT(*) > 1);

I get an error: #1093 - You can't specify target table 'comment' for update in FROM clause

WHERE comment_id IN

send me the same error

Note: I cant use 2 different query because I want use it in the Event Scheduler

Upvotes: 0

Views: 378

Answers (1)

Ryan-Neal Mes
Ryan-Neal Mes

Reputation: 6263

Something like this should get you going.

DELETE 
  c
FROM 
  comment c

  inner join (
    # some select statement that pulls all of your required comments
  ) comment_to_delete on
    comment_to_delete.comment_id = c.comment_id

Sorry not really following what you are trying to do in your code

Upvotes: 1

Related Questions