updating wp_options

wordpress-url-change-options.sql

/**
 * First of all change prefix if you have different one. As default WordPress has `wp_`
 */

// Update home and siteurl.
UPDATE wp_options SET option_value = replace(option_value, 'http://www.oldurl', 'http://www.newurl') WHERE option_name = 'home' OR option_name = 'siteurl';

// Update posts guid.
UPDATE wp_posts SET guid = replace(guid, 'http://www.oldurl','http://www.newurl');

// Update post contents.
UPDATE wp_posts SET post_content = replace(post_content, 'http://www.oldurl', 'http://www.newurl');

// Update postmeta.
UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://www.oldurl','http://www.newurl');

// Transfer usermeta to new key.
UPDATE wp_usermeta SET meta_key = 'new_key_name' WHERE meta_key = 'old_key_name';

// Delete Orphaned Post Meta Data in WordPress
SELECT * FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;
DELETE pm FROM wp_postmeta pm LEFT JOIN wp_posts wp ON wp.ID = pm.post_id WHERE wp.ID IS NULL;

// Update postmeta specific key and value.
UPDATE wp_postmeta SET meta_value = replace(meta_value, 'old_value', 'new_value') WHERE `meta_key` LIKE 'your_key';