Mass updating values in your WordPress database

Often when we migrate a site from one domain to another we have to update the values in the database to reflect the URL of the site we are now going to be using. Nowadays we typically use WP Migrate DB Pro to do all the heavy lifting but sometimes we do it manually and this is how we do it.

I cannot stress enough the important of backing up your database first before you do this and also you should be experienced in using PHPMyAdmin or equivalent so you can restore the database easily in the case of an issue. We will not be responsible for anything going wrong if you do use this method.

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';
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.old-domain.com', 'http://www.new-domain.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://www.old-domain.com', 'http://www.new-domain.com');

Firstly copy the above code into a text editor as this will make it easier to update the values. Once you have added to the text editor then you need to update the table prefix’s to match your table prefix. In our example we are using the typical ‘wp_’ prefix but you might be using something different like ‘mywpprefix_’ etc. Now go ahead and change the 3 values after the word UPDATE on each line to reflect your prefix.

Your next step is to change the section ‘http://www.old-domain.com’ for each line to the old domain name that the site was sitting on. Using Find & Replace in a text editor is a good way of doing this, making sure that you keep the url within ”.

Now update the ‘http://www.new-domain.com’ url to reflect the url of the new server. Again, Find and Replace is fine or you can manually update the values.

Once you have done these things then you are ready to go into the database and run the SQL query. Go to PHPMyAdmin or an equivalent and select your database. Its at this point that I would always do an export of the database in case anything goes wrong. You should save a SQL dump to your computer and once its done there should be a SQL tab on the right hand side and this is where you can paste your SQL query into and then click on run/execute.

The query wont take long and will typically let you know that the values have been updated and how many fields have been affected. I always check its worked by going to the wp_options table and making sure that the siteurl and home url values have been updated. If they have been updated then go to your site at the new url and check its working correctly. If its not working then something may have gone wrong and you can restore your database from the backed up version.

We hope you find this useful and here is the warning again about this as things can go very wrong very quickly if you don’t know what you are doing

I cannot stress enough the important of backing up your database first before you do this and also you should be experienced in using PHPMyAdmin or equivalent so you can restore the database easily in the case of an issue. We will not be responsible for anything going wrong if you do use this method.