I’ve been doing some webmaster maintenance – backups, software upgrades, etc. and ran into a WordPress install that had over 20,000 comments sitting in moderation. Ooops.
Unfortunately, WordPress provides no simple way of cleaning up huge amounts of comments. Mass edit mode is limited to the number of entries that can be deleted at once. Mass edit mode would be so much cooler if it had a big shiny button that says “Delete All Comments Awaiting Moderation”, don’t ya think?
Anyways, since I’ve faced similar problems in the past, I figured I would post my solution for our readers and anyone else looking for a quicker way to clear huge amounts of comments from a WordPress install:
To Delete All Comments Awaiting Moderation in WordPress:
(Remember to make a backup of your DB first)
From phpmyadmin, select your WordPress database, click the sql tab, and enter the following query:
SELECT *FROM wp_comments WHERE comment_approved ="0"
That will select all the un-moderated comments. Then enter this query:
delete from wp_comments WHERE comment_approved ="0"
Voila. Run a check and repair on the WordPress database afterwards to be sure everything is working fine.
This solution has worked for me on various versions of WordPress, and saved me hours of manual “mass edits”.