Before getting started
There are several ways to run SQL queries. Most of you probably have a cPanel installed on your server. This is the case if your host is Vidahost, A Small Orange or In Motion Hosting, for example.
To access phpMyAdmin from cPanel, simply log in to cPanel and click the phpMyAdmin icon in the Databases section.
Once you’ve entered PhpMyAdmin, you first have to select your blog database, then click on SQL tab to display the page which allows you to run any kind of queries.
Another option is to use a plugin named SQL Executioner. As the name says, the aim of this plugin is to allow you to run SQL queries on your WordPress database from within the Dashboard.
Important things to note:
- Always have a fresh backup of your database when applying those SQL queries. You can do so manually or use a WordPress plugin such as WP-DBManager.
- This article uses default table prefix
wp_. Make sure you change the prefixes to match the ones used by your database.
Delete all comments with a specific url
Spam is definitely a problem ith WordPress blogs, happily SQL is here to help.
DELETE from wp_comments WHERE comment_author_url LIKE "%spamurl%" ;
Source: WPRecipes
Delete all trackbacks
Most people, including me, think that trackbacks are useless. Clean up your blog database with that nifty query.
DELETE FROM wp_comments WHERE comment_type="trackback";
Source: WordPress.org Forums
Close trackbacks on all posts at once
Even better than deleting them, you can close all trackbacks at once with this query.
UPDATE wp_posts SET ping_status = 'closed';
Source: Dig WP
Bulk delete all unapproved comments
Too lazy to check all of your unapproved comments? This is the super fast way to delete them all at once.
DELETE from wp_comments WHERE comment_approved = '0';
Source: WordPress.org Forums
Bulk delete all comments marked as spam
Got 5400 spam comments and don’t want to go through dozen of pages to delete them? This query is for you.
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
Source: Dig WP
Delete all post revisions and associated data
Make your database lighter by removing post revisions and all associated data. A query I run on my blogs every 3-6 months.
DELETE a,b,c FROM wp_posts a LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id) WHERE a.post_type = 'revision';
Source: Solidly Stated
Remove unused shortcodes in post content
Shortcodes are really cool, but at some point you need to be able to remove the unused ones. Rather than editing each of your posts, run this simple query! Replace [tweet] by the unused shortcode to remove.
UPDATE wp_post SET post_content = replace(post_content, '[tweet]', '' ) ;
Source: WPRecipes
Replace a word by another in post content
This can be very useful to update a link, for example.
UPDATE wp_post SET post_content = replace(post_content, 'old_word', 'new_word' ) ;
Add a custom field to all posts
If you’re always using a specific custom field, you better add it on all posts at once rather than editing X amount of your posts. Here you go!
INSERT INTO wp_postmeta (post_id, meta_key, meta_value) SELECT ID AS post_id, 'MyCustomField' AS meta_key 'myvalue AS meta_value FROM wp_posts WHERE ID NOT IN (SELECT post_id FROM wp_postmeta WHERE meta_key = 'MyCustomField') `` AND post_type = 'post';
Source: The Customize Windows
Delete very old posts
Are all your posts very outdated? Here’s an easy way to remove them. For optimal results, you should definitely use 301 redirections to redirect deleted posts to your homepage or updated versions of those posts.
DELETE FROM wp_posts WHERE post_date < '2010-01-01 00:00:00' AND post_status = 'publish'
Source: xarj.net
Get a list of all commentators’ emails
Not really that you should use email of your commentators for anything (except with their consent) but here’s the way to get a list of all emails stored in the wp_comments table. Note the use of DISTINCT to make sure the query won’t return any duplicates.
SELECT DISTINCT comment_author_email FROM wp_comments;
Source: Dig WP
Assign posts to a new author
If for some reason you want to transfer posts from an author to another, this query is yours. You need to update this query with both the old author and new author IDs before running it.
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';
Source: WPRecipes
Update user password
Quick way to update any password. Don’t forget to replace username by the user name of the user you’d like to update the password.
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'username';
Source: WordPress Support
Batch disable all plugins
In case something goes wrong with your blog, it can be a good idea to deactivate all your plugins, in case one of those was the source of the problem. Here’s how you can batch disable them using a SQL query.
UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';
Source: WPRecipes
Downsize your database by removing transients
Transients are a simple and standardized way of temporarily storing cached data in the database by giving it a custom name and a timeframe after which it will expire and be deleted. But sometimes, transients set by WP and countless plugins can take a lot of space in your database. Good news, transients can be safely removed with this simple query.
For more information about Transients, check out the Codex.
DELETE FROM `wp_options` WHERE `option_name` LIKE ('%\_transient\_%');
Source: Stack Overflow
Get rid of unused tags
As your database is probably filled with lots of tags you don’t use anymore, you should consider cleaning it with the following:
DELETE 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: WPMU Dev
Change all your urls/domain names
WordPress stores absolute urls within the database. Which means that if you change your blog domain name, you’ll have to update every absolute url. Using SQL, this is extremelly simple to do. Just edit the three queries below and update the old and new domain names, then run it. Done!
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsite.com', 'http://www.newsite.com') WHERE option_name = 'home' OR option_name = 'siteurl'; UPDATE wp_posts SET guid = replace(guid, 'http://www.oldsite.com','http://www.newsite.com'); UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldsite.com', 'http://www.newsite.com');
Source: Smashing Magazine