What SQL Query to do a simple find and replace

Question

Whenever I create a new website I first create a staging site on a subdomain like "stage.domain-name.com".

After everything works correctly I export the database, open it in notepad++ and do a find/replace for "subdomain.domain-name.com" and replace it with "domain-name.com"... finally I import it into a new database for the live site.

My question is... what SQL query would I need to run if I just wanted to do this simple find/replace on the entire database using phpmyadmin?

-CH

Answer 1

The table where your URL is saved is wp_options. You should do an update on the columns that use the URL for your site:

UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "siteurl"
UPDATE TABLE wp_options SET option_value = "new domain" WHERE option_name = "home"

I might be missing some value, but whenever you do this find/replace process again, you can notice the values and tables that should be updated and add them to this script.

WordPress Codex has a nice guide on how to change a site URL, maybe that's even handier for you: Changing the Site URL

Answer 2

Best to do options, posts, post content and post meta:

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

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');

UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

Also see Searching Data using phpMyAdmin and MySQL | Packt Publishing. And Search RegEx is a good WP plugin to be able to search and replace with Grep through all posts and pages.

Update 6/16/2015: Using the tool linked in the next sentence is much better, as a simple find/replace as above in a database dump will break serialized data. See interconnectit.com WordPress Serialized PHP Search Replace Tool. This way, you don't break serialized data and won't need to run RegEx on post content, as the interconnect script changes URLs everywhere. I use that tool all the time to migrate sites to different domains, or to simply do a global change from http to https to force SSL without plugins and change all URLs in content to prevent insecure element errors.

Edit 9/02/21 WP CLI is also a great tool, and it correctly handles serialized data. See https://developer.wordpress.org/cli/commands/search-replace/

Answer 3

This is a great drop-in script that I use and it works beautifully with the serialized arrays that WP uses to store options. Just make sure to delete it from your remote server when you're done because it's a HUGE security risk.

https://interconnectit.com/products/search-and-replace-for-wordpress-databases/

Answer 4

For this I use WP-CLI because I find it the easiest and it takes care of serialized data.

wp search-replace 'http://example.dev' 'http://example.com' --skip-columns=guid

There is also an option that writes your changes into an SQL file instead of manipulating the actual database:

wp search-replace foo bar --export=database.sql

Answer 5

you do not have to do this , you can use relative paths.

when you are linking something instead of subdomain.soemthing.com/image.jpg - use /image.jpg for example

like this you won't face the problem in the first place.

otherwise for a mysql update statement you can use

update TABLE_NAME set FIELD_NAME = replace(FIELD_NAME, ‘find this string’, ‘replace found string with this string’);

Answer 6

To change the wordpress domain what we often need, may be to make the site live from localhost: This is a complete list of update queries:

UPDATE wp_posts SET guid = replace(guid, 'http://olddomain.com','http://newdomain.com');
UPDATE wp_posts SET post_content = replace(post_content, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_url = replace(link_url, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_links SET link_image = replace(link_image, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');
UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://olddomain.com', 'http://newdomain.com');

/*UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com') WHERE option_name = 'home' OR option_name = 'siteurl' OR option_name = 'widget_text' OR option_name = 'dashboard_widget_options';*/
UPDATE wp_options SET option_value = replace(option_value, 'http://olddomain.com', 'http://newdomain.com');

• We have to also add other tables which are not default with WP if necessary.

UPDATE: Search Replace DB version 3.1.0 is a user-friendly, front-end tool for developers, that allows you to carry out database wide search/replace actions, that don't damage PHP serialized strings or objects.

Answer 7

Actually, you don't have to use a SQL query just some adjustments in wp_config and functions.php file in your theme. Check out this topic in Wordpress Codex: https://codex.wordpress.org/Changing_The_Site_URL