I’ve been looking for the SQL query instructions for phpmyadmin on how to mass delete posts for wordpress blogs according to specific date range. A friend gave me a functional sql query which I have tested and works perfectly.
BACKUP YOUR WORDPRESS DATABASE before typing in any SQL Query! Read this article to correctly backup your WordPress Database.
With the following SQL query, you’ll be able to verify how many items will be deleted in your database. Notice the “SELECT” which simply tells the database to count the lines which will be deleted. This SQL query will count the wordpress posts published before June 6, 2008.
SELECT count(*) FROM wp_posts WHERE post_date < '2008-06-06 19:18:00' AND post_status = 'publish'
Once you are certain you want to mass delete the posts in your wordpress database, use the following SQL Query. This SQL query will delete the wordpress posts published before June 6, 2008.
DELETE FROM wp_posts WHERE post_date < '2008-06-06 19:18:00' AND post_status = 'publish'
Notice the two phpmyadmin sql queries are exactly the same except for the first argument. The first uses SELECT to count the posts and the second actually DELETE the posts.
There are many different SQL queries which would work, these are not the only ones!
Note that these queries delete the posts but some elements will remain like the meta data associated with the wordpress posts. These meta data are situated in the wp_postmeta table in your wordpress database. I do not know how to remove these elements as well. If anyone knows a better SQL query which works in phpmyadmin, please let us know in the comment form!