Useful wordpress SQL Queries for all bloggers – part 2

This is the continuation of my previous post on Useful WordPress SQL Queries. Hope you might have tried SQL commands from my previous post without manipulating your database.

Here we will continue with other useful wordpress sql queries. I will contine the numbering form my previous post.

6) Change the path of images:

What if you want to use CDN (Content Delivery Network) to display your images and uploaded all your images to your CDN space. You cannot go to all post individually and change the image path.

Execute below command to change all your images path across all posts in your blog

UPDATE wp_posts SET post_content = REPLACE (post_content, ‘src=”http://www.oldsiteurl.com’, ‘src=”http://yourcdn.newsiteurl.com’);

You will also need to update the GUID for the type “attachment” with the following SQL statement:

UPDATE wp_posts SET guid = REPLACE (guid, ‘http://www.oldsiteurl.com’, ‘http://yourcdn.newsiteurl.com’) WHERE post_type = ‘attachment’;

7) Change the default user name “admin”

Are you annoyed with your default login name “Admin” no worries just execute below command.

UPDATE wp_users
SET user_login = ‘your new name‘
WHERE user_login = ‘Admin’;

Change “your new name” to your desired name.

8) Delete all revisions of a post

Post revisions are a useful feature, but if you don’t delete revisions from time to time your database will quickly become very big. There are plugin’s like Wp-Optimize for deletion of these revisions.

But if you want to delete it manually use following query.

DELETE a, b, c FROM wp_posts the
Wp_term_relationships LEFT JOIN b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = ‘revision’

Source from: Lester chan

Note: Bear in mind that all revisions from each post will be deleted, including all of its meta data.

9) Update Post Meta

Updating Post Meta is almost the same way as updating the URL in post content. You can use the follow query to change all of the Post Meta.

UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, ‘http://www.oldsiteurl.com’,'http://www.newsiteurl.com’);

10)Delete Post Meta

Plugin’s who might not be using them, we all use them. When you does not like any installed plugin we will be removing them, it is a very common task for WordPress users.

Some of the plugin’ss use the post meta to store data pertaining to the plugin. After you have removed the plugin, those data are still left inside the post_meta table. Which will no longer be needed.

Run the following query to clean up such unused post meta values. This will help to speed up and reduce the size of your database.

DELETE FROM wp_postmeta WHERE meta_key = ‘your-meta-key’;

Hope you enjoyed my previous and this post on Useful WordPress SQL Queries. I have collected such Useful WordPress SQL Queries.

I am testing them on my test blog, as most of the queries were posted on source sites long time back. So testing them on latest version of WordPress, will be back soon with such working Useful WordPress SQL Queries.