Like most content management systems, WordPress is powered by a MySQL database. All of the WordPress data including the posts, comments, categories, user details and settings are stored within the MySQL database.
We often see developers new to WordPress on the hunt for valuable tips and ticks when it comes to applying database updates to WordPress sites. So the team here at OnePoint decided to share our favourite SQL queries for effective WordPress development. Yes, there’s probably a SQL queries below that you’re already familiar with, but take a look at the list below and hopefully they can help you with any migrations or development you may be undertaking.
10 best SQL Queries that all WordPress developers should know!
01. Change Site Url & Home Url
Each WordPress site stores the absolute path of the site URL and the home URL. If you’re migrating your WordPress site from one domain to another (e.g. Localhost to your live server), your site will not show online unless you update the Siteurl and Homeurl records in the database. Your site will still be pointing to your original domain (in this case the localhost) – change the site URL and Home URL for the site to work.
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';
02. Change GUID
After you have migrated your site from one domain to another (e.g. Localhost to www.yoursite.com.au), 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’s entered wrongly.
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsite.com', 'http://www.newsite.com');
03. Change URL in Content
WordPress uses absolute path instead of a relative path in the URL link when storing them in the database. Within the content of each post, WordPress stores all the old URLs referencing the old source. Therefore you will need to change all these URLs to your new domain location.
UPDATE wp_posts SET post_content = REPLACE (post_content, 'http://www.oldsite.com', 'http://www.newsite.com');
04. Change Post Meta
Updating Post Meta works pretty much 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. This is very handy for plugins that display images inside the WordPress. You’ll notice you may have some broken images when working in WordPress for some plugins, run the query below and this will fix any issues you may have.
UPDATE wp_postmeta SET meta_value = REPLACE (meta_value, 'http://www.oldsite.com','http://www.newsite.com');
05. Change Image Path Only
Some sites use a Content Delivery Network to deliver webpage images. The advantage of this is it offloads the delivery of image from your server (MaxCDN, Amazon Cloudfront, Cachefly to name a few). Obviously in this case your images are stored on an external directory. In the case that you move all website images across to a CDN, you’ll need to update your image paths to point there.
UPDATE wp_posts SET post_content = REPLACE (post_content, 'src="https://www.oldsite.com', 'src="https://yourcdn.newsite.com');
Also, don’t forget to update the GUID for Image Attachment with the following query:
UPDATE wp_posts SET guid = REPLACE (guid, 'http://www.oldsite.com', 'http://yourcdn.newsite.com') WHERE post_type = 'attachment';
06. Change Username
If you need to change your default WordPress username, simply use the SQL query below.
UPDATE wp_users SET user_login = 'New Username' WHERE user_login = 'Old Username';
07. Reset Password
Need to reset your password in WordPress? No drama just use the code below!
UPDATE wp_users SET user_pass = MD5( 'new_password' ) WHERE user_login = 'your-username';
08. Delete all Pingbacks
If your website receives plenty of pingback’s (your articles are popular) the size of your database increases. To help reduce the size of your database you can clear all pingbacks using the following SQL query.
DELETE FROM wp_comments WHERE comment_type = 'pingback';
09. Removing Comments and Spam
If your site has plenty of unwanted comments spam comments, going through each page to delete them can be tedious and time consuming. Use the SQL query below and those unwanted spam comments will be removed instantly.
0 = Comment Awaiting Moderation
1 = Approved Comment
spam = Comment marked as Spam
DELETE FROM wp_comments WHERE comment_approved = 'spam';
10. Assign all articles by one author to another
Transferring article ownership from one author to another can be very time consuming. That’s where the below SQL query becomes very handy. Using the code below allows you to scan through all database records and reassign authorship.
First you’ll need to obtain the author ID of both authors by going to your Users page in your WordPress admin panel. Click on the users’s name to view their profile. At the address bar, look for “user_id“. That is the author ID information we require; it will appear as a number.
UPDATE wp_posts SET post_author = 'new-author-id' WHERE post_author = 'old-author-id';
If you’re in need of some Brisbane web experts for an upcoming project, contact a OnePoint team member today on (07) 3444 0045.
