Wordpress SQL queries

Bulk delete all comments from wordpress

During migration of wordpress between hosts or upgrades you will come across situation where you simply want to get rid of all old comments.

Wordpress provides a UI where you can go and select the comments one by one and delete it. We need something quick and fast.

Just run the following two SQL queries using PHPmyadmin or any SQL editor

truncate table wp_comments;
truncate table wp_commentmeta;

The above two statements will get rid of all comments including pings.

Bulk assign category to all old wordpress posts

In a recent work for a client, we had to update all old posts and assign a category or ‘Legacy’ or ‘Archived’. The idea was that once we assign them with this category we could then display a notice using a filter to add a note at the beginning of the post that this is an old post and may not be relevant anymore.

It is always better to delete any old or irrelevant posts but in this situation we had to keep them.

First, go into wordpress and create the new category that you wish to assign and get the category id. Let say the new category is “legacy” and the corresponding id is 205. Also assume we need to identify all posts older than ‘2010-01-01’.

Use the following SQL query

insert into wp_term_relationships(object_id,term_taxonomy_id,term_order)
SELECT id,205,0
FROM `wp_posts`
where post_date <'2010-01-01'
and post_type='post'
order by post_date desc

The above SQL will get the ID of each post that meets the criteria and then it creates a record in the wp_term_relationships table.