20 Time-Saving WordPress SQL Query Snippets

WordPress stores every single scrap of information within a MySQL database. Posts, pages, comments, shortcodes, plugin settings… absolutely everything. The WordPress backend is fantastic and does allow you to manage and edit everything with ease, but only up to a certain point.

Say you have hundreds or even thousands of posts within your database, and you need to make site-wide global changes. Making each edit via the WordPress Dashboard can be time-consuming and does open up the possibility of mistakes occurring. If you do need to make site-wide edits, then it’s time to roll up your sleeves and delve directly into the WordPress MySQL database.

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 you have used to personalize your site. No matter how confident you are in your ability to use SQL queries, always remember to backup your WordPress database first!

Here are some resources to help you backup WordPress:

• WordPress Backups – Here you will find detailed instructions to backup your WordPress Site and your WordPress database as well as resources for automatic WordPress backups (plugins).
• Free WordPress Backup Solutions – These free WordPress backup plugins cater to every need and website.

You might also like these useful .htaccess snippets & hacks or these snippets that make WordPress user-friendly for your clients.

Add a Custom Field to All WordPress Posts & Pages

This snippet will add a custom field to every post and page found in your WP database. All you have to do is replace the UniversalCutomField to whatever Custom Field name you like to create, and then change MyCustomFieldValue to the value of your choice.

For posts only, use this snippet…

…and for pages only, use this code…

Source

Delete WordPress Post Meta

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

Source

Identify Unused WordPress Tags

In a WordPress database, if you run a query to delete old posts, like the one above, the old tags will remain. This query allows you to identify all of the unused tags.

Batch Delete WordPress Spam Comments

This little snippet is a life-saver. All you have to do to delete them all is run this SQL command:

Source

Batch Delete All Unapproved WordPress Comments

This SQL query will remove all unapproved comments and not touch the approved comments.

Source

Disable WordPress 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 2016-01-01 to suit your needs.

Source

Disabling & Enabling WordPress Trackbacks & Pingbacks

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

Globally enable pingbacks/trackbacks for all users:

Globally disable pingbacks/trackbacks for all users:

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

Source

Delete WordPress 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 particular URL. The '%’ means that any URL containing the string within the '%’ signs will be deleted.

Identify & Delete WordPress Posts that are over 'X' Days Old

If you ever need to identify and delete posts over a certain number of days old, 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 number of days you are looking for:

To delete any posts that are over 'X' amount of days, run this query:

Source

Removing Unwanted WordPress Shortcodes

WordPress shortcodes are great, but if 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 unusedshortcodes with your own shortcode name.

Source

Change Your WordPress Posts Into Pages and Vice-Versa

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

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

Source

Change Author Attribution On All WordPress 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:

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.

Source

Batch Deleting WordPress 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.

Source

Disable or Enable All WordPress Plugins

If you have ever encountered 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.

Source

Changing the Destination URL of a WordPress Site

Once you’ve moved your blog (template files, uploads & 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:

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

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

Change the Default 'Admin' WordPress 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:

Manually Reset your WordPress Password

If you have only a single user on your WordPress installation, and the login name is 'admin,' you can reset your password with this simple SQL query. Once executed, it will replace PASSWORD with your new password.

Source

Search and Replace WordPress Post Content

To search and replace post content, use the following code. Replace OriginalText with the current text and replace NewText with your new text.

Changing the URL of WordPress Images

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