WordPress : 10+ life saving SQL queries

by Jean-Baptiste Jung. 63 Comments -

Although there’s lots of things that you can do in WordPress, sometimes you may need a quick fix for a specific problem. In those cases, working directly on the database can be a real life-saver. In this article, I’m going to show you 10+ extremely useful SQL queries for WordPress.

How to execute SQL queries

For those who don’t know yet, SQL queries have to be executed within the MySQL command line interpreter or a web interface such as the popular PhpMyAdmin. Since we’re going to work on WordPress, you should note that the SQL Executionner plugin provides an easy-to-use interface that allows you to run SQL queries directly on your WordPress blog dashboard.

Although all the queries from this article have been tested, don’t forget that you shouldn’t test any of those on a production blog. Also, make sure that you always have a working database backup.

Manually change your password

It may sound like the thing that only happens to others but forgetting a password can happen to any of us. In case you lost your blog admin password, the only solution is to create a new one directly in your MySQL database.
The following query will do it. Notice that we use the MD5() MySQL function to turn our password into an MD5 hash.

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

Source : http://www.wprecipes.com/how-to-manually-reset-your-wordpress-password

Transfer posts from one user to another

Most WordPress newcomers tend to use the good old “admin” account instead of creating an account with their real name. If you made that mistake and created another account later, you can easily transfer your old “admin” posts to your new account with the SQL query below.
You’ll need the user id of both your old and new accounts.

UPDATE wp_posts SET post_author=NEW_AUTHOR_ID WHERE post_author=OLD_AUTHOR_ID;

Source : http://www.wprecipes.com/how-to-change-author-attribution-on-all-posts-at-once

Delete post revisions and meta associated to those revisions

Post revisions are very useful, especially in the case of a multi author blog. However, the problem of post revisions is definitely the number of database records it creates. For exemple, if your blog has 100 posts, which has 10 revisions each, you’ll end up with 1000 records in the wp_posts tables, while only 100 of them are necessary.
Executing this query will delete all post revisions as well as all meta info (custom fields, etc) associated to it. The whole process will result in a consequent gain of database space.

DELETE a,b,c FROM wp_posts a WHERE a.post_type = 'revision' LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id) LEFT JOIN wp_postmeta c ON (a.ID = c.post_id);

Source : http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Batch delete spam comments

Imagine that you’re coming back from holidays, where you haven’t had any access to the Internet. If you haven’t installed Akismet and depending on your blog popularity, you may end up with 1000, 2000 or even 10,000 comments to moderate.
You can spend a whole day to moderate the lot, or you can use this life-saving query to delete all unapproved comments. And for your next holidays, don’t forget to install Akismet!

DELETE from wp_comments WHERE comment_approved = '0';

Source : http://www.wprecipes.com/mark-asked-how-to-batch-deleting-spam-comments-on-a-wordpress-blog

Find unused tags

Tags are recorded on the wp_terms table. If for some reason a tag has been created but is not used anymore, it stays in the table. This query will let you know which tags are on the wp_terms table without being used anywhere on your blog. You can delete those safely.

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 : http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Find and replace data

This tip isn’t specific to WordPress and is a must know for anyone who’s working with MySQL databases. The MySQL function replace() lets you specify a field name, a string to find, and a replacement string. Once the query is executed, all occurrences of the string to replace will be replaced by the replacement string.
In case of a WordPress blog, this can be useful to batch replace a typo (For example people who repeatedly call the software WordPress…) or an email address.

UPDATE table_name SET field_name = replace( field_name, 'string_to_find', 'string_to_replace' ) ;

Source : http://perishablepress.com/press/2007/07/25/mysql-magic-find-and-replace-data/

Get a list of your commentators emails

Have you ever received unsolicited emails from blogs you previously commented? I’m sure you did, just like me. The fact is that getting a list of emails from your commentators is extremely easy using the following query. The DISTINCT parameter will make sure that we’ll only get each email once, even if the user commented more than once.
Please note that this is only a proof of concept: Don’t send your users unwanted emails.

SELECT DISTINCT comment_author_email FROM wp_comments;

Source : http://www.onextrapixel.com/2010/01/30/13-useful-wordpress-sql-queries-you-wish-you-knew-earlier/

Disable all your plugins at once

When things go wrong, especially on a production site, you have to be quick. Considering the fact that plugins are often the source of problems, disabling all your plugins in a second can prevent lots of problems.
Just run the following query:

UPDATE wp_options SET option_value = '' WHERE option_name = 'active_plugins';

Source : http://www.wprecipes.com/how-to-disable-all-your-plugins-in-a-second

Delete all tags

In WordPress, tags are recorded in the wp_terms tables, along with categories and taxonomies. If you wish to remove all tags, you can’t simply empty or delete the wp_terms as you’ll destroy categories at the same time!
If you want to get rid of your tags, run this query. It will remove all tags and relationships between tags and posts, while leaving categories and taxonomies intact.

DELETE a,b,c
FROM
	database.prefix_terms AS a
	LEFT JOIN database.prefix_term_taxonomy AS c ON a.term_id = c.term_id
	LEFT JOIN database.prefix_term_relationships AS b ON b.term_taxonomy_id = c.term_taxonomy_id
WHERE (
	c.taxonomy = 'post_tag' AND
	c.count = 0
	);

Source : http://wordpress.org/support/topic/311665

List unused post meta

Post meta is created by plugins and custom fields. They are extremely useful, but they can quickly make your database grow in size. The following query will show you all the records in the postmeta table that doesn’t have corresponding records in the post table.

SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

Source : http://wordpress.org/support/topic/337412

Disable comments on older posts

Everyone who has been in blogging for more than one year will know: Even after some months, your old posts still receive interest from the public and lots of comments, mostly because they are indexed by search engines. This is a good thing of course, but the problem is for people like me who own technical blogs and have to answer lots of questions related to their old (and sometimes obsolete) posts.
The solution to this problem is to automatically close comments on posts which are too old. This SQL query will close comments on all posts published before January 1, 2009.

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

Source : http://perishablepress.com/press/2008/02/20/wordpress-discussion-management-enable-or-disable-comments-and-pingbacks-via-sql/

Replace commentator url

Previously in this article, I talked about the very useful replace() MySQL function. Here is a good example of how useful it is : Let’s say you previously own a site and used its url in your comments to generate backlinks to this site.
If you sell the site, you can easily replace the old url by your new site url. Simply run this query and you’ll be done!

UPDATE wp_comments SET comment_author_url = REPLACE( comment_author_url, 'http://oldurl.com', 'http://newurl.com' );

Source : http://perishablepress.com/press/2008/07/14/wordpress-link-author-comments-home-page/

Replace commentator email adress

Another good example of the replace() function. This query will replace the email adress provided in the comments field, by a new one.

UPDATE wp_comments SET comment_author_email = REPLACE( comment_author_email, 'old-email@address.com', 'new-email@address.com' );

Source : http://perishablepress.com/press/2008/05/18/wordpress-tip-update-email-address-in-the-wordpress-database

Delete all comments with a specific url

Lately, I’ve noticied that some clever spammers left some quite relevant comments, but with a link pointing to a viagra site. Unfortunely, when I noticied it the commentator already left lots of comments. The following query will delete all comments with a specific url. The “%” signs means that any url containing the string within the % signs will be deleted.

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

Source : http://perishablepress.com/press/2007/07/25/mysql-magic-find-and-replace-data/

Comments (63) - Leave yours

  5. Codesquid said:

    Great tips! With phpMyAdmin it’s really easy to just go in and edit values directly, you didn’t even really have to know SQL. I like being able to manually edit all data directly!

  6. Jacob Guite-St-Pierre said:

    Again, when calling a date, set $wpdb to global use “.$wpdb->prefix.” to get the correct table prefix.

    For example:
    global $wpdb;
    $sql = “UPDATE ‘”.$wpdb->prefix.”users’ SET ‘user_pass’ = MD5(‘PASSWORD’) WHERE ‘user_login’ =’admin’ LIMIT 1;”

    ;)

  7. Ashfame said:

    1 question : How can someone change his password when he is not even logged in WP and if the user is why he/she wouldn’t do from the WP backend itself?
    lolz!
    BTW, I am a fan of MySQL too but I prefer shell access :)

    • Jean-Baptiste Jung said:

      Imagine you’re the site admin, and forgot your password, and can’t access to your email adress? Ok, this doesn’t happens very often, but believe me, the day it happen, you’ll be happy to know how to change your password directly from the database ;)

      • Daniel K said:

        I’ve gone away before on vacation/weekend with it set up on localhost and couldn’t remember the password. Instead of having to dump the whole thing and start over you just edit the password nad away you go.

  10. Christian said:

    I thought passwords in WordPress were salted now? But there’s no salt in the SQL listed for changing the password..?

  11. Travis Berry said:

    Other good ones to know are the ones to move your WordPress install across domains.

    Update to new location
    UPDATE wp_options SET option_value = replace(option_value, ‘http://www.old-domain.com’, ‘http://www.new-domain.com’) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;

    Fix post and pages urls
    UPDATE wp_posts SET guid = replace(guid, ‘http://www.old-domain.com’,'http://www.new-domain.com’);

    Update absolute urls in posts
    UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.old-domain.com’, ‘http://www.new-domain.com’);

  14. Justin Watt said:

    Jean-Baptiste, just FYI “Executioner” (note the double-n) is misspelled in the link to the WordPress SQL Executioner ;)

    To Jacob et al, with the SQL Executioner, you don’t have to worry about table prefixes if you use the handy dollar-sign variable interpolation (similar to PHP). So rather than something like DELETE from wp_comments WHERE comment_approved = '0'; where your table prefix may or may not be “wp_” do this: DELETE from $comments WHERE comment_approved = '0'; This tip is especially handy if you’re trying to help someone else clean up their db, and you’re not sure what their table prefix is.

  15. Metric Stormtrooper said:

    you last example is wrong. Instead of

    DELETE from wp_comments WHERE comment_author_url = “%wpbeginner%” ;

    its must be

    DELETE from wp_comments WHERE comment_author_url LIKE “%wpbeginner%” ;

    if you want to use wild cards or case-insensitive comparison in SQL you must use LIKE. The = operator only returns exact matches.

  22. Gustavs Cirulis said:

    I use this a lot to transfer database from one site to another:

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

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

    UPDATE wp_posts SET post_content = replace(post_content, ‘http://www.old-url.com’, ‘http://www.new-url.com’);

  27. Jean-Michel Mermet said:

    Hi,
    how can I delete all comments of a post in wordpress ? I have an urgent need for this …

    Could you please help me ?

    Thanks a lot

  35. Brad Proctor said:

    Good tips. I actually needed that one about manually changing the password just the other day. I had a client with a messed up WordPress installation and couldn’t log in. Thanks for the tips. I’ll have to remember these for next time.

  38. loige said:

    Manually change your password
    UPDATE 'wp_users' SET 'user_pass' = MD5('PASSWORD') WHERE 'user_login' ='admin' LIMIT 1;

    Does not wordpress uses any kind of md5 salt??? ( http://en.wikipedia.org/wiki/MD5 )

    password will results really vulnerable…

  40. Cat. said:

    Identify Unused Tags

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

    Solution:

    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.coun

    How to Delete them ?

  45. Veronica @ studiob13 said:

    Okay. First off, your tip “Manually change your password” was a lifesaver. I completely forgot a password for a Database that I had setup awhile back. It was a totally painful experience setting up the whole thing initially, so the option of starting over from scratch was an unsightly idea… this definitely helped. I’m not a sql guru, so tips like these help. Thanks.

  48. Jack said:

    Okay, I admit that it’s a bit intimidating for me to engage in SQL queries, but after seeing how detailed the instructions are I have decided to give it a try. Thanks a lot for the useful SQL queries for WordPress, I hope there won’t be any problems whatsoever.

  49. jerman said:

    Thank you, this sql queries are useful, here you can use following SQL queries to change your wordpress url:

    UPDATE wp_options
    SET option_value = replace(option_value, ‘http://www.oldurl.com’, ‘http://www.newurl.com’)
    WHERE option_name = ‘home’ OR option_name = ‘siteurl’

  50. vince jelenic said:

    hi, as a monkey see monkey do kinda coder (i.e no real idea what i’m doing, but I know what i want to do :-) ran into small stumbling block.
    We sell antiques online, and are using wordpress posts as containers for our items for sale. We update those products often, through an importer I built for csv files.
    Simple problem exists. Due to various tests and imports now, we have over 8000 posts and 137,000 post_meta table etries .
    Figure it needs cleaning. Came across this snippet to LIST post metas which are NOT linked to existing post. Works great, but how do I delete this found listing? Phmyadmin pops me into a window where no tool to checkmark or delete seems to exist ?

    SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

    The result works, I get 3000 recorrds returned.
    So what next step do I do to DELETE these now?

  51. JonC said:

    Thanks for sharing the tips you found in your travels on your own site, I just split a site into multiple sites and needed to get rid of a lot of tags and now I’m all done!

  52. Rob Aid said:

    Here is a descent sql query you need in order to change the main admin account if you had a default WordPress instalation (admin as a username) and want to counter brute force hacking of your account:

    UPDATE wp_users SET user_login = ‘New Username’ WHERE user_login = ‘Admin’;

  55. sheryl said:

    How could I pull all database entries/changes from a certain date range from a compressed database backup file
    into a similar, live, database?
    To explain: my webhost somehow started directing traffic from my friends blog onto a server that had an old version of the website (from an account that had been closed) and an old database.
    User posts and account information were written to that old database.
    I need to move that information back to the correct, live, website and database..

    Any Help??!!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Please respect the following rules: No advertising, no spam, no keyword in name field. Thank you!