There are endless reasons for editing the WordPress database, but there are always those SQL queries that are needed more than the rest. And that is what we have for you today.

Always Backup WordPress First

Your WordPress database stores every single one of your carefully written posts, every comment from your loyal readers and every setting that you have used to personalize your site. So, no matter how confident you are with your ability to use these SQL queries, please, please, always remember to backup your WordPress database first.

Here are some resources to help yo backup WordPress:

· WordPress Backups – Here you will find detailed instructions to back up your WordPress Site and your WordPress Database as well as resources for automatic WordPress backups (plugins).

· How to Backup the WordPress Database – PhpMyAdmin – This less detailed tutorial will show you how to backup your WordPress database in phpMyAdmin.

· WP-DB-Backup Plugin – WP-DB-Backup allows you to automate the backup of your core WordPress database.

Add a custom field to all posts and pages

This useful snippet will add a custom field to both posts and pages within your WP database. All you have to do is replace the ‘UniversalCutomField‘ to whatever name you like and then change ‘MyValue‘ to the value of your choice.

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyValue AS meta_value FROM wp_posts
WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField');

For posts only, use this snippet…

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyValue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
`` AND post_type = 'post';

…and for pages only, use this code…

INSERT INTO wp_postmeta (post_id, meta_key, meta_value)
SELECT ID AS post_id, 'UniversalCustomField'
AS meta_key 'MyValue AS meta_value
FROM wp_posts WHERE ID NOT IN
(SELECT post_id FROM wp_postmeta WHERE meta_key = 'UniversalCustomField')
AND `post_type` = 'page';

Source

Delete Post Meta

When you install or remove plugins they make use of the post meta to store data. After you have removed the plugin, the data will still remain inside the post_meta table, which of course, is no longer needed. Remember and change ‘YourMetaKey‘ to your own value before running this query.

DELETE FROM wp_postmeta WHERE meta_key = 'YourMetaKey';

Source

Identify Unused Tags

In a WordPress database, if you run a query to delete old posts, as the one above, the old tags will remain and appear in your tag cloud/listing. This query allows you to identify the unused tags.

SELECT * From wp_terms wt
INNER JOIN wp_term_taxonomy wtt ON wt.term_id=wtt.term_id WHERE wtt.taxonomy='post_tag' AND wtt.count=0;

Source

Batch Deleting Spam Comments

This little snippet was a life-saver a few weeks back when we had over 60,000 (really!) spam comments.
All you have to do is run this SQL command:

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

Source

Batch Deleting All Unapproved Comments

This SQL query will remove all of yourthe unapproved comments and will not touch your approved comments.

DELETE FROM wp_comments WHERE comment_approved = 0

Source

Disable Comments on Older Posts

For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the date by editing the 2010-01-01 to suit your needs.

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2010-01-01' AND post_status = 'publish';

Source

Disabling and Enabling Trackbacks & Pingbacks

For this query, specify the comment_status as either open, closed, or registered_only.

Globally enable pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = 'open';

Globally disable pingbacks/trackbacks for all users:

UPDATE wp_posts SET ping_status = 'closed';

Enable/Disable Pingbacks & Trackbacks Before a Certain Date

For this query, specify the ping_status as either open or closed. Also, specify the date by editing the 2010-01-01 to suit your needs.

UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2010-01-01' AND post_status = 'publish';

Source

Delete Comments With A Specific URL

If you have spam comments that all contain the same URL then this query allows you to remove them in one go. The following query will delete all comments with a specific url. The ‘%’ means that any url containing the string within the ‘%’ signs will be deleted.

DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;

Source

Identify & Delete Posts that are over ‘X’ Days Old

If you ever need to identify and delete posts that are over a certain amount of days old, then this snippet will help.

To identify any posts that are over ‘X’ amount of days run this query, remembering to replace the ‘X’ with the amount of days you are looking for:

SELECT * FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > X

To delete any posts that are over ‘X’ amount of days run this query:

DELETE FROM `wp_posts`
WHERE `post_type` = 'post'
AND DATEDIFF(NOW(), `post_date`) > X

Source

Removing Unwanted Shortcodes

WordPress shortcodes are great, but when you decide to stop using them, their code will stay within your post content. Here is a simple SQL query to run on your database to get rid of any unwanted shortcodes. Replace ‘tweet‘ with your own shortcode name.

UPDATE wp_post SET post_content = replace(post_content, '[tweet]', '' ) ;

Source

Change Your WP Posts Into Pages and Vice-Versa

Changing posts to pages is very easy, all you have to do is run this short SQL query through PHPMyAdmin:

UPDATE wp_posts SET post_type = 'page' WHERE post_type = 'post'

… and if you want to change pages to posts use this snippet:

UPDATE wp_posts SET post_type = 'post' WHERE post_type = 'page'

Source

Change Author Attribution On All Posts

The first thing you will need to do for this snippet is retrieve the IDs of the WordPress author. You can find this out by using the following SQL command:

SELECT ID, display_name FROM wp_users;

Once you have the old and new IDs, insert the command below, remembering to replace ‘NEW_AUTHOR_ID‘ with the new authors ID and ‘OLD_AUTHOR_ID‘ with the old.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Source

Batch Deleting Post Revisions

Post revisions can be very useful, but they also considerably increase the size of your MySQL database. You could manually delete posts revisions, but a much quicker method would be to use this SQL query.

DELETE FROM wp_posts WHERE post_type = "revision";

Source

Disable or Enable All WordPress Plugins

If you have ever been faced with the white screen of death and found yourself unable to login to the WordPress Admin after activating a new plugin, then this snippet will certainly help you. It will disable all plugins instantly, allowing you to log back in.

UPDATE wp_options SET option_value = 'a:0:{}' WHERE option_name = 'active_plugins';

Source

Changing the Destination URL of a WordPress Site

Once you’ve moved your blog (template files, uploads & the database) from one server to another, the next thing you will then need to do is to tell WordPress your new address.

Remember and change ‘http://www.old-site.com‘ to your old URL, and the ‘http://www.new-site.com’ to your new URL.
The first command to use is:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.old-site.com', 'http://www.new-site.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Then you will have to change the url from the table wp_posts with this snippet:

UPDATE wp_posts SET guid = replace(guid, 'http://www.old-site.com','http://www.new-site.com);

And finally, you’ll need to do a search through the content of your posts to be absolutely sure that your new URL link is not messing with the old URL:

UPDATE wp_posts SET post_content = replace(post_content, ' http://www.ancien-site.com ', ' http://www.nouveau-site.com ');

Source

Change the Default ‘Admin’ Username

Every WordPress installation will create an account with the default Admin username. Being able to change this default username, will give your WordPress admin panel additional security.

Change ‘YourNewUsername‘ to your new name:

UPDATE wp_users SET user_login = 'YourNewUsername' WHERE user_login = 'Admin';

Source

Manually Reset your WordPress Password

If you’ve only a single user on your WordPress installation, and the login name is admin. You can reset your password with just this simple SQL query, which once executed will replace PASSWORD with your new password.

UPDATE `wordpress`.`wp_users` SET `user_pass` = MD5('PASSWORD') WHERE `wp_users`.`user_login` =`admin` LIMIT 1;

Source

Search and Replace Post Content

To search and replace within the post content area of WordPress use the following code. Replace ‘OriginalText‘ with the original text you would like to change and replace ‘ReplacedText‘ with your new text.

UPDATE wp_posts SET `post_content`
= REPLACE (`post_content`,
'OriginalText',
'ReplacedText');

Source

Changing the URL of Images

If you need to change the paths of your images, you can use this SQL command below:

UPDATE wp_posts
SET post_content = REPLACE (post_content, 'src=”http://www.myoldurl.com', 'src=”http://www.mynewurl.com');

Source