Delete all rows except

Question

When I execute this query:

DELETE FROM `wp_posts` WHERE id NOT IN 
  (SELECT id FROM wp_posts WHERE post_status = 'publish')

I get the following error message:

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

Not sure what the syntax issue is here.

Answer 1

This can be done without using a sub-query. Please try the following

DELETE FROM 'wp_posts' WHERE post_status != 'publish'