What SQL Query to do a simple find and replace

  • 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?


    If you're not familiar with queries try the Search and Replace plugin, http://wordpress.org/extend/plugins/search-and-replace/

    Why not use WordPress functionality to update the URL? http://codex.wordpress.org/Moving_WordPress details everything

    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: https://wordpress.org/plugins/better-search-replace/

  • 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

    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

    Check out that new link I added on the answer. I think that would be the way to go.

    This will not work for serialized data. It might completely break some theme configuration.

License under CC-BY-SA with attribution

Content dated before 6/26/2020 9:53 AM