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.

The problem is that you can't have the same table in a subquery during a DELETE. The way the queries are handled would cause undefined behaviour if it were allowed. — Polynomial
– Polynomial, Commented Apr 28, 2012 at 17:50
gee, i wish the error message would say it that clearly...error messages for dummies? — keruilin
– keruilin, Commented Apr 28, 2012 at 17:52

Chetter Hummin · Accepted Answer · 2012-04-28 17:50:52Z

18

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

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

answered Apr 28, 2012 at 17:50

Chetter Hummin

6,8478 gold badges34 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ahh, yes, i tried that approach but got the != syntax wrong...thanks!