How to delete all unpublished comments?

Question

I have literally thousands of unpublished comments. I am wondering what is the easiest way to git rid of them altogether.

Thanks

Answer 1

For Drupal 6, run the following PHP code:

db_query('DELETE FROM {comments} WHERE status = 1');

For Drupal 7, run the following PHP code:

db_query('DELETE FROM {comment} WHERE status = 0');

You can also execute the query using Drush's sql-query command, for example

drush sql-query "DELETE FROM {comment} WHERE status = 0"

Answer 2

The better way I think is to use Views and Views Bulk Operations and you can do a simple admin interface to delete all of them.

Otherwise, you still can delete directly throw a SQL query.

Answer 3

For those using Drupal 7, using VBO has changed and the above comments about choosing Bulk Operations as the view style is not correct. Here are the steps to create a VBO page with Drupal 7 (I'll use the example of deleting all comments)

• Create a view where you show Comments and create a page
• You'll need to format it in a way that allows you to add fields (table is a handy format)
• Add a Bulk operations: Comment field and for the settings for this field, under SELECTED BULK OPERATIONS choose Delete item (you can opt to skip the confirmation step here)
• If you have a lot of comments, under Pager for this view display something like 200 at a time (it will be slow but you won't have to repeat your deletes lots of times)
• Save your view and then go to the page you created. You should be able to select all the items and then choose Delete under operations. You can then confirm the delete and repeat as necessary.

Answer 4

you can review this steps

• Install the views bulk operations module,
• create a page view of comments, and choose "Bulk Operations" as the view style.
• Make sure you select "Delete comment" as one of the selected operations.
• When you view the view page you created, you'll be able to bulk delete comments.

Regards

Answer 5

For Drupal 7, this is really easy to achieve.

1. Navigate to Content > Comments > Unapproved comments.
2. Select the upper left radio button to select all rows in the table.
3. Select "Delete the selected comments" in the drop down list at the top.
4. Hit the 'Update' button.

Answer 6

I would usually recommend to use VBO to delete comments or nodes in bulk but if you are in a situation when you have hundreds of thousands of comments and you don't have too much time, here is a sql query which will delete all unapproved comments along with all the revisions and data related to those comments which in my case was taking 1.2Gb of space in DB

DELETE c, rcb, dcb
FROM
    comment AS c
JOIN field_revision_comment_body AS rcb ON (c.cid = rcb.entity_id)
JOIN field_data_comment_body AS dcb ON (rcb.entity_id = dcb.entity_id)
WHERE
    c. STATUS = 0

Answer 7

Actually this worked for me:

drush sql-query "DELETE FROM comment WHERE status = 0"

i.e. no curly braces required.

However as explained elsewhere there are other tables in the db that reference comments so this approach is not advisable.

maybe there needs to be a module to do this properly, including field_data_comment_body, field_revision_comment_body etc Would be useful if you have a gazillion unpublished comments.

In the meantime this post here shows a better way:

DELETE FROM field_data_comment_body USING field_data_comment_body JOIN comment ON comment.cid=field_data_comment_body.entity_id AND comment.status=0;

DELETE FROM field_revision_comment_body USING field_revision_comment_body JOIN comment ON comment.cid=field_revision_comment_body.entity_id AND comment.status=0;

DELETE FROM comment WHERE status=0;

https://www.drupal.org/forum/general/general-discussion/2010-05-16/mass-delete-spam-comments#comment-10964889