13 Useful WordPress SQL Queries You Wish You Knew Earlier

13 Useful WordPress SQL Queries You Wish You Knew Earlier

WordPress is driven by a MySQL database. This is something active WordPress users would know. However, if you only just read about it here from us, here’s what you should know. MySQL is a free relational database management system available in most web hosting services. All of the WordPress data like the posts, comments, categories, and settings are stored within the MySQL database. If you wish to read up more about MySQL, you can take a look at WordPress’s Database Schema.

WordPress SQL Queries

For example, if you needed to change some information across the board in WordPress, going through each record is very time consuming and prone to human error. By simply executing a SQL query against your WordPress database, you can make the necessary changes quickly and efficiently.

Shown below are some SQL queries that can be of great assistance to you when using WordPress.

Backup your WordPress Database

Before you proceed with any changes, be sure to backup your database. It is a good practice to always backup your database before making any major changes. This ensures that even if anything were to go wrong, you would still be able to restore it.

You can download WP-DB-Backup or WP-DBManager plugin to backup your database through your WordPress admin panel. Alternatively, you can backup your database through phpMyAdmin manually.

If you decide to backup your WordPress database manually, follow these steps:

  1. Login to your phpMyAdmin.
  2. Select your WordPress database.
  3. Click on Export at the top of the navigation.
  4. Select the tables you want to backup, or select all tables to backup the whole database.
  5. Select SQL to export as .sql extension.
  6. Check the “Save as file” checkbox.
  7. Choose compression type, select gzipped to compress the database to a smaller size.
  8. Finally click Go, and a download window will prompt you to save your backup database file.

13 SQL Queries for WordPress

An easy way to run any of the queries below is to use phpMyAdmin. phpMyAdmin is one of the most common database look-up and editing tool. Almost all the web hosting services’ control panels provide this. There is also WordPress SQL Executioner – a WordPress plugin that allow you to execute your SQL query.

To use phpMyAdmin follow the steps below:

  1. Login to phpMyAdmin panel and select your WordPress database.
  2. Click on the SQL tab which will bring you to a page with a SQL query box.

Once you see the SQL query box, like the image shown below, you can run your SQL query there.

SQL Query Box

Note: All queries are direct SQL queries and will assume the standard table prefix ‘wp_’ for tables. If you are using a different table prefix, please modify queries accordingly.

Change Siteurl & Homeurl

WordPress stores the absolute path of the site URL and home URL in the database. Therefore, if you transfer your WordPress site from the localhost to your server, your site will not load online. This is because the absolute path URL is still pointing to your localhost. You will need to change the site URL and the home URL in order for the site to work.

Solution:

UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com') WHERE option_name = 'home' OR option_name = 'siteurl';

Change GUID

After you have migrated your blog from the localhost to your server or from another domain to a new domain, you will need to fix the URLs for the GUID field in wp_posts table. This is crucial because GUID is used to translate your post or page slug to the correct article absolute path if it is entered wrongly.

Solution:

UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

Change URL in Content

WordPress uses absolute path in the URL link instead of a relative path in the URL link when storing them in the database. Within the content of each post record, it stores all the old URLs referencing the old source. Therefore you will need to change all these URLs to the new domain location.

Solution:

UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsiteurl.com', 'http://www.newsiteurl.com');

Change Image Path Only

If you decide to use Amazon CloudFront as your Content Delivery Network (CDN) to offload the delivery of images from your server. After your have created your CNAME record, you can use the query below to change all the image paths in WordPress to load all your images from Amazon CloudFront.

Solution:

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 Image Attachment with the following query:

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

Update Post Meta

Updating Post Meta works almost the same way as updating the URL in post content. If you have stored extra URL data for each post, you can use the follow query to change all of them.

Solution:

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

Change Default “Admin” Username

Every default WordPress installation will create an account with a default Admin username. This is wide spread knowledge, everyone who uses WordPress knows this. However, this can be a security issue because a hacker can brutal force your WordPress admin panel. If you can change your default “Admin” username, you will give your WordPress admin panel additional security.

Solution:

UPDATE wp_users SET user_login = 'Your New Username' WHERE user_login = 'Admin';

Reset Password

Ever wanted to reset your password in WordPress, but cannot seem to use the reset password section whatever the reason?

Solution:

UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';

Assign all articles by Author B to Author A

If you want to transfer the articles under Author B to merge with those under Author A, it will be very time consuming if you do it article by article. With the following SQL query, you can easily go through all the records and assign articles by Author B to go under Author A.

You will first need to obtain the author ID of both authors by going to your Author & User page in your WordPress admin panel. Click on the author’s name to view their profile. At the address bar, look for “user_id“. That is the author ID information we require.

Solution:

UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';

Delete Revision

When you are editing an article in WordPress, there will be many revision copies being saved. This is a waste of resources because excessive revision records can increase the burden of the database. Over time, when you have thousands of entries, your database will have grown significantly. This will increase loop iterations, data retrieval and will affect the page loading time.

Solution:

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 from: Lester chan

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

Delete Post Meta

Installing or removing plugins is a very common task for WordPress. Some of the plugins make use of 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 the unused post meta value. This will help to speed up and reduce the size of your database.

Solution:

DELETE FROM wp_postmeta WHERE meta_key = 'your-meta-key';

Export all Comment Emails with no Duplicate

Over a period of time, your blog will have received many comments. These comments will include the email addresses left by the commenter. You can retrieve all these emails for your mailing list without any duplicate.

Solution:

SELECT DISTINCT comment_author_email FROM wp_comments;

Once you have the result, under Query results operations, select export to export all the emails in phpMyAdmin.

Delete all Pingback

Popular articles receive plenty of pingback. When this happens, the size of your database increases. In order to reduce size of the database, you can try removing all the pingbacks.

Solution:

DELETE FROM wp_comments WHERE comment_type = 'pingback';

Delete all Spam Comments

If you have plenty of spam comments, going through each page to delete spam can be tedious and frustrating. With the following SQL query, even if you have to face deleting 500 over spam comments, it will be a breeze.

Solution:

DELETE FROM wp_comments WHERE comment_approved = 'spam';
  • 0 = Comment Awaiting Moderation
  • 1 = Approved Comment
  • spam = Comment marked as Spam

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.count=0;

Start Querying!

If you need to do some maintenance and clean up or move WordPress across domains, the above SQL queries can be useful and help speed things up for you. If you have any additional handy SQL queries for WordPress, please share it with us below.

Comments

  1. / Reply

    Thank you for sharing ;)
    Maybe this code useful, grab current week posts :

    $paged = (get_query_var(‘paged’)) ? get_query_var(‘paged’) : 1;
    $thisWeek = date(‘W’);
    $newsposts = query_posts(‘cat=-44&showposts=’ . $limit . ‘&paged=’ . $paged .’&w=’.$thisWeek);

    • DaveBowman,
    • February 1, 2010
    / Reply

    OMG, so WordPress is that much popular nowadays that instead of “SQL queries (to manage WordPress by hand)” the article is titled “WordPress queries”? (… with phpMyAdmin in opening illustration?)

    Half of the article is about plain old simple text replacement, and the other half… “Delete Revision”? Come on, you wordpressers have a brazillion of plugins for all happenstances from tweeting to sneezing and – what – no plugin to delete revisions? Surprising :)

    “Identify Unused Tags” although is a very nice and useful query. Let me guess – the rest is so the post would not consist of just one snippet? Even so, thanks for the up – probably this will come in handy at some point in the future.

      • Wiley,
      • January 18, 2011
      / Reply

      Dave you old cracker you! :P Seriously though I get what you’re saying but we all know you’re a ‘closet wordpresser’! I’m a blog subscriber, u can’t fool me you old pedal pusher. Bowman I swear, you crack me up. LOL

      Rich

    1. / Reply

      Wp-Optimize will delete all post revisions, change out usernames and do many of the functions manually described here too.

  2. / Reply

    Good post. Bookmarked.

  3. / Reply

    This is a very useful post and can you please write a detailed post on how to back up wordpress database using php my admin with using photo, as it make it easy for us.

  4. / Reply

    Wow thank you for this post. Very very useful info.

    I was wondering if you know of any magic SQL strings for helping move a WP site from a sub directory site.com/v2 to the main directory site.com/ ?

    Would updating the siteurl work for this?

    thanks
    Jon Spooner

    1. / Reply

      Yes it will.

  5. / Reply

    Of course with PhpMyadmin you don’t even have to run queries, you can just edit the fields directly. Sometimes this is the easiest way to do things!

    • Jonas,
    • February 2, 2010
    / Reply

    Very good tutorial :) Can I translate it into French for my blog (xoodeo.com) ?

    1. / Reply

      Sure.. a link back to this article will be great ;)

        • Jonas,
        • February 2, 2010
        / Reply

        Of course for the link :D Thanks a lot !

  6. / Reply

    hi!
    By which version can use it?
    2.8? or 2.9?

  7. / Reply

    Hi, my name is Marcos, I’m from Mexico and I wonder if I can use part of this blog to post it into my blog. My blog is focused in design and programming and I ‘d like to post some of the code, of course, I ‘d put the link to the original article

    :)

    1. / Reply

      Sure. ;)

    • paul,
    • February 3, 2010
    / Reply

    this one is fun too
    MYSQL=”mysql -h HOST -u USERNAME -pPASSWORD -D DB_NAME”
    $MYSQL -BNe “show tables” | awk ‘{print “set foreign_key_checks=0; drop tabl `” $1 “`;”}’ | $MYSQL
    unset MYSQL

    1. / Reply

      IDIOT I JUST LOST ALL MY TABLES ARGHGHGH UFFFFFFFFFFF

      • ds,
      • January 22, 2012
      / Reply

      DON’T DO THIS … This deletes all tables

    • paul,
    • February 3, 2010
    / Reply

    just kidding of course, it will delete all tables!

    1. / Reply

      me too! :D

  8. / Reply

    Good post. twitted

    • SMiGL,
    • February 3, 2010
    / Reply

    Nice collection tips. Thanks

  9. / Reply

    RT’d. Great tips. Thank you so much!

  10. / Reply

    Excelent,
    really great collection

  11. / Reply

    Interesting collection of queries, thank you. One thing as I am reading your content, if you could add some padding-left because the content goes all the way to the edge of my screen.

    Just a heads up.

    1. / Reply

      Thank you for the feedback.

  12. / Reply

    A question from an ordinary user – that is, I am not a programmer.
    When I download my backup and unzip it, I have a sql file which I can read as text using TextPad.
    Is there some way to read the contents as a GUI interface?
    In other words, can I recreate the blog and its functionality on my desktop?
    Also is there some way that I can get a simple list of the post headers?
    Any comments would be much appreciated … but please keep them very simple.
    Thanks in advance
    Peter

    1. / Reply

      First you’ll need to install MySQL on your local computer.

      Next, import the sql file you downloaded into your localhost through phpMyAdmin.

      To get the post headers, run this query:

      SELECT post_title FROM wp_posts WHERE post_type = ‘post’;

      • DaveBowman,
      • February 6, 2010
      / Reply

      “can I recreate the blog and its functionality on my desktop?”
      Yes, Peter, you could do that by installing software that emulates web-server on your computer. There are several popular software packages that do that: WAMP, XAMPP and others. Here’s the link to a really nice and detailed tutorial on how to install WordPress locally:
      http://sixrevisions.com/tutorials/web-development-tutorials/using-xampp-for-local-wordpress-theme-development/

      You could then import your SQL-file from a live server into local MySQL using PHPMyAdmin.

      Also note, that you will have to change the siteurl of your site from “something.com” to “localhost”. Here’s a tutorial to do the opposite (from localhost to something.com)
      http://jestro.com/convert-local-wordpress-xampp-installation-into-live-site/
      but it is easy to figure out how to do it the other way.

      Of course, if you have a custom theme and images on your live server you will have to just copy the whole folder with WordPress and put it in your htdocs folder in the first place.

      Alas, couldn’t keep the comments very simple, but it’s just because the matter itself isn’t :)

  13. / Reply

    Very useful post for me. Thanks!

  14. / Reply

    Thanks alot for sharing, if been looking for a summary like this for ages.

    Again thanks for sharing

    • Ben,
    • February 11, 2010
    / Reply

    Some really useful queries, especially for a wordpress and phpMyAdmin n00b such as myself.

    Thank you, keep up the good work :)

  15. / Reply

    Great, this help me a lot for changing my subdomain. Really useful, thanks!!!

    PD: Can I translate some parts of your post into my blog? I will put the link to your article :)

    • Tony,
    • February 19, 2010
    / Reply

    Thanks for sharing! Found the content to be very useful! :)

  16. / Reply

    How can I query any user, and change them from a contributor to an administrator..

    • Jim,
    • March 8, 2010
    / Reply

    “WordPress SQL queries”? Ehm, no, simply SQL queries to run in MySQL.

    And no, WordPress is not “driven” by MySQL. WordPress stores data in MySQL databases. WordPress could be said to be “driven”, or preferrably, run with PHP.

    I do think this matters.. Otherwise, thanks for the useful snippets.

    • sushilshirbhate,
    • March 9, 2010
    / Reply

    Thanks for sharing! Found the content to be very useful

    • ben,
    • March 11, 2010
    / Reply

    I found an issue with your queries, the post meta change will break if your old url and new url are not the same character length. as it is a serialized array if you change the length you have to change the number specified for the string as well

  17. / Reply

    Thanks for this info, I am working on a new wordpress blog and these SQL Queries are proving to be very useful, thanks again :)

  18. / Reply

    good topic

  19. / Reply

    thx. for sharing all this information is very usefull for me that just learning to blog using wordpress.

    • ukm,
    • April 7, 2010
    / Reply

    thank you
    really worth post, I need some of queries..

  20. / Reply

    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 ?

  21. / Reply

    As it is a serialized array if you change the length you have to change the number specified for the string as well

  22. / Reply

    WoOoOoOoW
    nice share ,
    plz put more sql for wordpress
    really amazing post
    thanks

  23. / Reply

    good topic
    thx

  24. / Reply

    Never thought about changing the default ‘admin’ name :) Nice one. Will use it. Thanks for sharing.

      • Sam,
      • September 6, 2010
      / Reply

      Suneel,
      with wordpress 3 this is not more necessary as you can choose your “admin” username when installing – however with previous version 2.9 and less “admin” was set and that was a big security leak. So changing this to something different is recommended anyway – my wordpress got hacked almost certainly becasue of that – but then again – the sites were on MT and they have troubles anyway lately with hacks and stuff. Anyone else here unhappy with MT?

    • Raul,
    • June 14, 2010
    / Reply

    Very nice and useful queries…

    Do you know the query to mass import taxonomies to db?
    Adding a few hundred taxonomies one by one will kill me…

    Maybe with php&mysql I can do this.

  25. / Reply

    Nice collection, some of them I’ve know but the other are new for me, thank for that

  26. / Reply

    Just a quick note that the query for replacing image URLs will also change the URLs for any JavaScript which might be embedded in the post. Probably not that common a scenario, but maybe worth noting. Nice post!

  27. / Reply

    Thanks for sharing. It is very useful for me,,,, i need some revisions of my site so your article is very useful for me.

  28. / Reply

    Hi,

    We have followed the optimization procedure and our website is getting loaded very quickly…Thanks for your valuable post.

    Regards,
    FourPx Articles

  29. / Reply

    Thanks Terrance, You just saved my day was moving my wordpress file from localhost to a server and found that all my url was messed up was searching for solution and this post came to my rescue at the right time.

    Thanks Mate :)

  30. / Reply

    As a hardcore SQL coder this post scares me. Some of the queries you are showing will break the database if you have certain plugins installed that expect to be notified when data is updated. Sure SQL is powerful and I use it all the time but I also know how to fix any problems my wayward queries may create. Most people who would not know how to do the things you explain already are not in that category.

    To readers of the post, Caveat Emptor! Make *damn sure* you backup your database before you try any of this; don’t skip that step as so many of us are inclined to do…

  31. / Reply

    As a hardcore SQL coder this post scares me. Some of the queries you are showing will break the database if you have certain plugins installed that expect to be notified when data is updated. Sure SQL is powerful and I use it all the time but I also know how to fix any problems my wayward queries may create. Most people who would not know how to do the things you explain already are not in that category.

    To readers of the post, Caveat Emptor! Make *damn sure* you backup your database before you try any of this; don’t skip that step as so many of us are inclined to do…

    • Sam,
    • September 6, 2010
    / Reply

    Saved me already so much time – copy/paste – *bang* – done.
    Obviously need to be careful and go over manually under wp_options as certain bits need manual tweekings but overall great collection.

    • Lana,
    • September 16, 2010
    / Reply

    I ran across this post in search of code to help me accomplish something in my wp database.

    1. This post is a tremendous help to us php and mysql puppies, thank you so much.

    2. Could you possibly help me with my db issue as it is a simlar concept? In my wordpress mysql database, in the postmeta table, I have a meta_key that I would like to edit the value of and since there are multiple entries for this specific meta_key with this same value, I thought perhaps a sql query would be great instead of going thru all the many many pages to find and edit each individual one.

    e.g.
    mysql db name: db_name
    mysql db meta_key: meta_key (of course)
    mysql db meta_key present value: _mk_value

    Now lets say that I want to change _mk_value to mk_value. I am clueless as to how to write this query, or command if you will??? Your help would be really appreciated.

    1. / Reply

      Hi Lana,

      Here is the sql query:

      UPDATE db_name SET meta_value = ‘mk_value’ WHERE meta_key = ‘meta_key’;

      Remember to backup your database before running this query.

      Hope this help.

    • Lana,
    • September 16, 2010
    / Reply

    Thanks so much for taking the time to help me out Terrance. I knew there had to be an easier way to do that.

    • PB,
    • September 23, 2010
    / Reply

    Awesome post – tremendously helpful!

    One question – is there a way to remove duplicate pages from a WP blog through an SQL query?

    Thanks!

    • axfa,
    • September 24, 2010
    / Reply

    thanks for this collection
    nice

    • jarno,
    • September 24, 2010
    / Reply

    Thanks for useful post….
    My blog have an error like this :

    SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON tt.term_id = t.term_id INNER JOIN wp_term_relationships AS tr ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE tt.taxonomy IN (‘category’) AND tr.object_id IN (3891) ORDER BY t.name ASC

    What should I do ? Pls help

    thanks before

  32. / Reply

    This is a nice list of SQL queries and some of them I really wished to know earlier ;-) Thanks.

  33. / Reply

    thanks for teaching
    great post

  34. / Reply

    Good post. twitted

  35. / Reply

    This is a nice list of SQL queries and some of them I really wished to know earlier ;-) Thanks.

  36. / Reply

    Nice list of SQL queries. Thanks for sharing

    • t31os,
    • December 22, 2010
    / Reply

    Ditto what Mike said and there should be some clear warning preceeding this post to ensure users backup their data first, alot of these queries can be harmful, and ideally posts or terms in a taxonomy should be removed via the WordPress administration..

    Why? .. Because WordPress will correctly remove all the relational data and update all the appropriate counts that the MySQL queries won’t necessarily do.

    Also be sure you’re not running replace queries on serialized data, simply put, it can and possibly will break your site, as has already been highlighted in a couple of the above comments.

    • DG,
    • December 30, 2010
    / Reply

    Read something similar, didn’t go into as much detail tho. Thanks for the post.

  37. / Reply

    how to delete post below 50 characters from phpmyadmin

  38. / Reply

    alot of these queries can be harmful, and ideally posts or terms in a taxonomy should be removed via the WordPress administration..

  39. / Reply

    nice post : thanks : i bookmarked this

  40. / Reply

    Good article. Backing up my wordpress database is something I wish I had done earlier, would have saved me alot of time late last year!

    • jam,
    • January 25, 2011
    / Reply

    just thanks ! i am copying it ! ok ? thanks again

    • Margaret,
    • January 28, 2011
    / Reply

    I cannot get this to work:
    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;

    I get this error:
    Error in query: Syntax error near ‘\’post_tag\’ AND wtt.count=0′ at line 2

    I am not a techie person but I am desperate to delete unused tags

    1. / Reply

      How are you executing the query? It seems like you’ve escaped the quotes around post_tag too many times. The query should be

      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;

    • mark,
    • February 13, 2011
    / Reply

    A very good post!!!!
    Could you tell me how to query users to list all users with the ADMIN rights?

    Thanks,

    Mark

  41. / Reply

    Hey all..;)

    I have a WP install with 14k post categories.. I’m crap with mysql and was wondering if anyone had a query to delete all categories with say less than 3 posts in it.. or for all categories named ” .. for example.. thanks in advance..;)

    chemmy.

  42. / Reply

    I’m interested in this article. For me it was learning to develop my blog. Thank you.

  43. / Reply

    thanx.. very useful for newbie like me..

    • Ryan,
    • April 5, 2011
    / Reply

    Great Post, Bookmarked as “Bible”.

  44. / Reply

    Nice post! I just tried some of the queries…but they dont seem to work on wordpress 3.1+ ?!

  45. / Reply

    Good Jobs!

  46. / Reply

    How could I pull all wordpress database entries/changes from a certain date range from a compressed database backup file
    into a similar, live, wordpress database?

    To explain: my webhost somehow started directing traffic from my friends blog onto a server that had an old version of the worpress blog (from an account that had been closed). This old website connected to the old database. (The newer blog resides in a different account on a different server.)
    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??!!

    1. / Reply

      You would have to first import the backup into a separate database, then you can use INSERT INTO…SELECT as described in the MySQL manual (http://dev.mysql.com/doc/refman/5.1/en/insert-select.html). Just add the date range into your SELECT clause to grab the particular posts and then INSERT into the new database.

  47. / Reply

    What about this?
    Change URL from links:
    UPDATE wp_links SET link_url= REPLACE (link_url, ‘http://oldsiteurl.com’, ‘http://www.newsiteurl.com’);

    Change image from links:
    UPDATE wp_links SET link_image = REPLACE (link_image, ‘http://oldsiteurl.com’, ‘http://www.newsiteurl.com’);

  48. / Reply

    great post. any idea how i can import a database larger than 50mb? i get errors when i try to do this operation. something about extended inserts! any ideas? thanks in advance

      • Max,
      • July 15, 2011
      / Reply

      Steff,

      It would be easiest if you could re-do the dump with skip-extended-inserts option see http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html#option_mysqldump_extended-insert

      Failing that you would likely have to use the mysql command line client tool to import the data after setting some configuration settings (likely max packet) or split up the data yourself (which will not be really nice)

      You may want to think about hiring someone with MySQL administration experience to help with this.

    • Matt,
    • August 9, 2011
    / Reply

    I tried to delete my revision history using the code above but it seems that some informations about my links has been removed. Some links had been assigned categories but some links lost that relationship.

  49. / Reply

    hey,
    i have one question.
    how i can export posts from a blog but only atribuite one of author not all post?
    thank you

  50. / Reply

    Thanks, usually im using DELETE FROM wp_comments WHERE comment_approved = ‘spam’; to Delete all Spam Comments. It more simple than one by one from dashboard gui .

  51. / Reply

    I’m interested in this article. For me it was learning to develop my blog. Thank you.

  52. / Reply

    I liked this blog post. Keeping me entertained…

  53. / Reply

    Nice post bud. No wonder this is one of the most popular ones going on!

    • David,
    • September 3, 2011
    / Reply

    Why can’t you search the entire database, and replace “http://localhost/oldsite” with “http://www.newsite.com” wherever it appears?

    I guess I haven’t tested in enough circumstanced, but I believe I’ve seen plugins do that as well. To do the same thing, since I’m exporting/importing the file anyway, while it’s on my computer, I open it a text editor and do the find and replace.

    Anyway, just my thought. Of course site url and home url are often different, depending on install folder but that’s done after the fact.

    • r4i,
    • September 6, 2011
    / Reply

    This is a great one..nice article, thanks for share with us… I learn a lot with your words.. thank for the post.

  54. / Reply

    Very useful article for coders! Thank you so much for share with us.

    Best Regards,

  55. / Reply

    Next, import the sql file you downloaded into your localhost through phpMyAdmin.

    • Scott,
    • October 30, 2011
    / Reply

    Big help thanks for this…

    • mike,
    • November 1, 2011
    / Reply

    It was really helpful for me. Here working further i stuck at a point hope you could help.

    I want to delete couple of comment that include some specific words like say “buy” out of a big number those are waiting for approval.

    I dont want to go to each and every comment to see what shit a spammer left one my blog. I need a straight way by just putting a query and all useless comment could be deleted.

    • mhk,
    • November 3, 2011
    / Reply

    Excelent post
    thanks

    • mike,
    • November 10, 2011
    / Reply

    hey buddy.. you approved my comment but didn’t pay attention to what am I asking. No response…

    • Alan,
    • November 19, 2011
    / Reply

    I have used your queries before many times but only when moving wordpress sites/databases around on external hotsing not to my Ubuntu server which is what I want to do now. Normally goes well – just create a new site and database – import the old sql file to create the tables and then run the queries – in this case primarily changing http://www.myoldhostname.co.uk to http://localhost.
    In the past when I have run the site for the first time with the new structure it goes straight in but this time I am being given the WordPress Welcome screen. Any thoughts. wp-config is correct becasue I delete all the newly created tables before importing the old sql file.

    • Michael Otton,
    • December 14, 2011
    / Reply

    Finally some really useful info. Thankyou very much

  56. / Reply

    Hello All.
    Came across the site from google and though it has a lot of queries, I still can’t find the one I’m looking for and i’m hoping someone might help.
    What I’m trying to do, is delete a user and all posts related to that user. I ran “DELETE FROM `impactra_wrdpn`.`wp_users` WHERE `wp_users`.`ID` = theuserid” though user got deleted, the posts were still on the site. I know I could do it from admin backend but my allocation is not allowing me and these are all spam posts :(
    Please advice and Thanks in advance
    Samuel

  57. / Reply

    an awesome query list !!
    Thanks a lot :)

  58. / Reply

    Several of these queries are awesome for post host transfer updates. I know you change the URL via admin before moving it etc, but these are nice too! Thanks!

  59. / Reply

    Do you know a query to reset WP data to it’s original form, this includes removing all dummy content and postmetas?

  60. / Reply

    Thank you!

    Very useful post.

    Although I couldn´t get this query to work

    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;

    MySQL returns nothing ( it should )

  61. / Reply

    What a FANTASTIC list! I just started trying to learn more SQL/PHP just so I could fix many of the problems that you described here. I really wish I would of found this page a few months ago when I ran into the “image/url” extension problem – If I would of seen this post, would of saved me 6-8 hours, ROFL!!! Thanks again for sharing this list.

  62. / Reply

    I always come back to this damned list. :)

    Thanks

  63. I then discovered the Page Speed plugin and started using it when trialling new themes. I joined Elegant Themes and drooled over their designs, but couldn’t find a single theme. I really like the post thanks you are sharing a good post

    • Daniel,
    • March 18, 2012
    / Reply

    How do you get all post from user A and change their status from whatever to pending?

    • Mah,
    • April 7, 2012
    / Reply

    I just started trying to learn more SQL/PHP just so I could fix many of the problems that you described here. I really wish I would of found this page a few months ago when I ran into the “image/url” extension problem

    • Ron,
    • April 21, 2012
    / Reply

    My client has been using a custom field “publication_date” for posts that are actually news articles. Now he wants to copy this publication_date to the posting date in about 100 posts/articles. Any SQL query for this?

    And, a plug for “Search and Replace” plugin. Awesome tool!

    • sara,
    • April 21, 2012
    / Reply

    Good post. Bookmarked.
    I Love onextrapixel

    • Chris,
    • April 26, 2012
    / Reply

    I need your guidance on providing the query strings for the following :

    1. I wanted to delete older posts from 2008 to 2010 in the database as it’s bloated to over 400mb and a problem when backing up and importing into database. Deleting these would lighten this table and the dump as a whole.

    2. What is the query string to do the above?

    3. What’s the query string to delete any relevant traces related to all the deleted posts thereafter such as comments of the deleted posts, unused tags?

    Your kind assistance with your answers to the above, would be much appreciated.

    Thank you in advance and best wishes.

  64. / Reply

    really useful..thanks for sharing..all the best

    • Jon,
    • May 30, 2012
    / Reply

    I can see how this would be useful for WordPress database neophytes but it’s also the kind of thing I dread my clients who I’ve mentioned WordPress to discovering. I await the first request for access to phpMyAdmin with dread!

  65. / Reply

    I have some duplicated plugins after importing my old database. Can I delete the duplicated plugins with SQL?
    Thanks

  66. / Reply

    Thanks for sharing..all the best.. need some DB query to Change Permalink of WPSC powered website.

  67. / Reply

    I made a quick snippet to change url:

    //no secret, just put your old url and new
    set @oldurl = ‘http://oldwp-url.com’, @newurl = ‘http://newwp-url.com’;

    UPDATE wp_options SET option_value = replace(option_value, @oldurl, @newurl) WHERE option_name = ‘home’ OR option_name = ‘siteurl’;
    UPDATE wp_posts SET guid = REPLACE (guid, @oldurl, @newurl);
    UPDATE wp_posts SET post_content = REPLACE (post_content, @oldurl, @newurl);
    UPDATE wp_posts SET post_content = REPLACE (post_content, CONCAT(‘src=”‘, @oldurl), CONCAT(‘src=”‘, @newurl));
    UPDATE wp_posts SET guid = REPLACE (guid, @oldurl, @newurl) WHERE post_type = ‘attachment’;
    UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, @oldurl, @newurl);

  68. / Reply

    how about restoring the database (sql) uploaded on FTP, then using an SQL query?

    am i using the right command?
    mysql -u YOURUSERNAME -p YOURPASSWORD < PATHTOSQL.sql

    however, i'm getting this error:
    #1064 – You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near …. blahblahblah…. at line 1

    please help!

    • Tom,
    • September 9, 2012
    / Reply

    Great post, really handy. Thank you for sharing.

  69. / Reply

    Good!!Thank you for sharing.

    • Eoin,
    • November 9, 2012
    / Reply

    Yea, but is there any plugins that just allow you to update a table, I know of Table Reloaded but it isnt saved in a editable sort-of-way table, more like in one row. SQL is great for some of these changes I just dont get the URL change ones surely you can export FIND>REPLACE the IMPORT.

  70. / Reply

    The look so great i think these can really help out in future as well..!! but there might be many new things you can do now in WP SQL queries

    • John (Jean C),
    • April 14, 2013
    / Reply

    Am a newbie at web design and have an interesting problem. It’s a long story but in a nutshell, have transferred a site from a local host to a shared host and failed to notice that my transfer tools had some exclusions. I ended up with a site which does not have any tables for comments or commentsmeta.

    Is there any way to add the missing tables without overwriting the entire database?

  71. / Reply

    This iss all that a wordPress user need while moving his blog.

    • markey707,
    • July 1, 2013
    / Reply

    Have you got a query to select all posts that have a specific text string then to add a custom field to all those posts?

    • Rrezart Bozo,
    • September 14, 2013
    / Reply

    which sql query shows only categories in wordpress?
    which sql query shows post in one category?
    thank you!

    1. / Reply

      Check this website for your answer:

      http://codex.wordpress.org/Function_Reference/query_posts

      Ayesha

  72. / Reply

    SELECT DISTINCT comment_author_IP FROM wp_comments WHERE comment_approved=”spam”;

    Get all spam IPs

    • netyou,
    • October 9, 2013
    / Reply

    This has been the best article I have found for these issues by far.

    How about making a cheat sheet?

    • netyou,
    • November 26, 2013
    / Reply

    This is one of the most useful articles I have ever used!

    If any ones interested I wrote a small util for renaming urls based on your queries. It gets an old url and a new url and prints out the relevant queries for a rename.

    http://hitautodestruct.github.io/wordpress-db-migrate/

    • dskanth,
    • January 29, 2014
    / Reply

    Thanks for providing this useful information. Now my blog is more optimized.

  73. / Reply

    //Here’s my favorite, TURN ON ALL COMMENTS ON EVERY POST //

    UPDATE wp_posts SET comment_status=’open’ WHERE post_status = ‘publish’ AND post_type = ‘post’;

  74. / Reply

    Just want to say thank you for this article. It has helped me countless times.

    • Lukman Arif Sanjani,
    • November 16, 2015
    / Reply

    wow man… many thanks… (y)

Leave a Reply

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

Deals

Elegant Themes Coupon Code: 20% discount

While most other WordPress theme sites charge per theme, Elegant Themes is an amazing deal as you pay just once for access to 87 premium WordPress themes and a…

WrapPixel Coupon – 25% Discount

If you’re looking for an HTML or PSD template for your next website, checkout the selection of templates at WrapPixel made for web developers, designers, and agencies. You can…