Delete invalid records related self problem

Question

I have comments table. Structure like this

id  parent_id  user_id  comment  
--  ---------  -------  -------

I want to delete records where have incorrect related values.
For delete records where user_id is invalid value I use this query

DELETE FROM `comments` WHERE `user_id` NOT IN (SELECT `id` FROM `users`)

but when I try to delete records where parent_id is invalid data I use this query

DELETE FROM `comments` WHERE `parent_id` NOT IN (SELECT `id` FROM `comments`)

I get this error

Error Code: 1093 You can't specify target table 'comments' for update in FROM clause

This may have a duplicate but the suggested duplicate does not apply to `not in`. — Gordon Linoff, Sep 26 '19 at 20:44

score 1 · Accepted Answer · answered Sep 26 '19 at 20:23

You can probably do what you want with cascading foreign key constraints.

That said, you can accomplish this using left join:

DELETE c
    FROM comments c LEFT JOIN
         comments cp
         ON c.parent_id = cp.id
    WHERE cp.id IS NULL AND
          c.parent_id IS NOT NULL;

Note the condition c.parent_id IS NOT NULL. That is implied by (almost) any comparison you make on the column, including NOT IN.

Delete invalid records related self problem

1 Answers1