Useful queries for moving Wordpress between subdomains

To contact us Click HERE

Background: recently had to move several wordpress installations from a server where they had been set up as site.[domainname] to a server where they were to be the more usual www.[domainname] . This required tracking down a number of references in the database and changing them.
All together now:

UPDATE `wp_options` SET option_value = replace( option_value, '/site.', '/www.' );
UPDATE `wp_posts` SET post_content = replace( post_content, '/site.', '/www.' );
UPDATE `wp_posts` SET guid = replace( guid, '/site.', '/www.' ) where post_type = 'attachment';
UPDATE `wp_postmeta` SET meta_value = replace(meta_value, '/site.', '/www.' );

Individually with explanations:
UPDATE `wp_options` SET option_value = replace( option_value, '/site.', '/www.' );
To change the site_url and also any template settings that use a full-url reference to a resource. Also check for these in template files.

UPDATE `wp_posts` SET post_content = replace( post_content, '/site.', '/www.' );
Fix links to other pages in the site and also any files or images which aren't included as attachments

UPDATE `wp_posts` SET guid = replace( guid, '/site.', '/www.' ) where post_type = 'attachment';
You shouldn't change the guids of posts because it can mess with RSS feeds (may or may not be a problem depending on the site). But attachments representing attached images or files do need to have their paths changed. More information in the Wordpress codex which doesn't consider the case of a subdomain changing.

UPDATE `wp_postmeta` SET meta_value = replace(meta_value, '/site.', '/www.' );
This gets elements (often design elements like a page specific banner or sidebar image) which have been attached to posts/pages as custom fields

~~~
The other thing to be very aware of is that all the search engine links (and any other incoming links) to the existing site will point to site.[domainname]. Make sure to set up a "site" subdomain on the new server and 301 redirect it to www.[domainname]