WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


share|improve this question
If you're not familiar with queries try the Search and Replace plugin, wordpress.org/extend/plugins/search-and-replace – t31os Jan 26 '11 at 14:19
Why not use WordPress functionality to update the URL? codex.wordpress.org/Moving_WordPress details everything – Alex Older Nov 18 '14 at 15:35
There is a plugin for this. It allows comfortable use of the backend and also replaces the URL in the post-content and some other fields, if you want to: wordpress.org/plugins/better-search-replace – simonthesorcerer Jul 23 '15 at 20:33
up vote 11 down vote accepted

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

share|improve this answer
is there not a way to do a find/replace on the entire database? In other words... I noticed for example that I need to replace the URLs in a bunch of different locations including the media library.... If there was a find/replace for the entire database essentially for every field then this would solve the problem. Thanks for your help – NetConstructor.com Jan 25 '11 at 11:28
Check out that new link I added on the answer. I think that would be the way to go. – Fernando Briano Jan 25 '11 at 11:29

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.

share|improve this answer
Don't ever change the guid - even if going to a new domain. It's used to uniquely ID the post as the ID can change if the posts are being exported/imported into a new database. For one thing, RSS readers will use the GUID to tell if a particular article has been read or not. Changing the guid will effectively republish all your articles. – Taylor Dewey Jun 16 '12 at 17:42
@taylordewey said: "Don't ever change the guid..." Rubbish. – markratledge Jun 17 '12 at 18:55
@songdogtech Care to explain why it is rubbish? – shea Mar 6 '13 at 4:39
If you're changing domains, you simply don't have a choice and have to change GUIDs. Fallout from RSS readers is a minimal price to pay. – markratledge Nov 6 '15 at 20:20

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.


share|improve this answer
I don't know why I was -1'd. This script is much better than a SQL statement. Feedback please? – lancemonotone Aug 3 '11 at 14:10
a tool that people who dont know sql can use, is upsetting to those who write in sql – Jon Jun 23 '13 at 18:54

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’);
share|improve this answer
Thanks... yeah I will do that next time. The SQL statement does a find replacement on the entire database (including all tables)? – NetConstructor.com Jan 25 '11 at 12:03
@NetConstructor.com The SQL statement mireille gave you above is the generic MySQL command for replacing a string in a specific field in a specific table. If you tried running this statement exactly as it was written, it wouldn't work. For this command to work, you'd need to change the TABLE_NAME & FIELD_NAME to a real field and table used by WordPress. – Manzabar Jan 26 '11 at 3:15
Note also that even if you desire to use relative paths, lots of parts of wordpress tend to auto-insert full paths. To really get this working a plugin like: wordpress.org/plugins/root-relative-urls is very, very helpful – benz001 Sep 15 '14 at 5:03

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.
share|improve this answer

I too would recommend using a script like the one @lancemonotone mentioned earlier in order to avoid problems with certain widgets and plugins being affected by not serializing the URLs as you replace them.


Offers a great PHP script that can save you many hours of tedious rebuilding while moving complicated sites from production to live.

share|improve this answer

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

share|improve this answer
This isn't correct. WordPress unfortunately stores some URL in the database as text. – s_ha_dum Sep 30 '15 at 14:03

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

share|improve this answer

protected by Community Dec 3 '12 at 18:16

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.