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.