Deleting Unapproved Comments In Drupal 7

The first thing to do was to delete any unapproved comments from the comments table.

DELETE FROM comment WHERE status = 0;

Next, the comment body fields in the field_data_comment_body table also need to be removed. This was done by also cross referencing any missing comments from the comment table.

DELETE field_data_comment_body FROM field_data_comment_body
LEFT JOIN comment ON field_data_comment_body.entity_id = comment.cid
WHERE comment.cid IS NULL;

The comments also have revisions, which need to be deleted in the same way.

DELETE field_revision_comment_body FROM field_revision_comment_body
LEFT JOIN comment ON field_revision_comment_body.entity_id = comment.cid
WHERE comment.cid IS NULL;

I should note here that if you are doing this at home and you have added any other fields to your comments then you will have to look out for those tables as well.

Finally, if you have Mollom installed then you can also truncate the Mollom log table. This can be quite full of log records from the comments that you just deleted.

TRUNCATE TABLE mollom;