Mass URL change in WordPress from MySQL Database

I just spent 2 of my hours to figure this thing for my client’s WordPress website. One of our client’s site somehow got redirect after changing the URLs (from where it has been changed, he even doesn’t know that). So it was my job to fix that. And of course I fixed that, but in a very engineered way.

I first took a note which table and in which column I have that old url. Then I just build some MySQL query and executed that from phpMyAdmin.

I wrote the following queries to run to replace all old url to new url. You can do it for not only URL for any kinds of texts. But, DON’T forget to take a backup of your database.

UPDATE wp_options SET option_value = replace(option_value, 'http://my-old-url.com', 'http://new-url.com') WHERE option_name = 'home' OR option_name = 'siteurl';

UPDATE wp_posts SET post_content = replace(post_content, 'http://my-old-url.com', 'http://new-url.com');

UPDATE wp_postmeta SET meta_value = replace(meta_value,'http://my-old-url.com','http://new-url.com');

UPDATE wp_usermeta SET meta_value = replace(meta_value, 'http://my-old-url.com','http://new-url.com');

UPDATE wp_links SET link_url = replace(link_url, 'http://my-old-url.com','http://new-url.com');

UPDATE wp_comments SET comment_content = replace(comment_content , 'http://my-old-url.com','http://new-url.com');

UPDATE wp_posts SET guid = REPLACE(guid, 'http://my-old-url.com', 'http://new-url.com')

I wrote queries for only 2 tables but I created another few queries for you so based on TABLE and COLUMN you can replace your OLD VALUE with NEW VALUE by yourself.

The query format for who is not familiar with MySQL.

UPDATE TABLE SET COLUMN_NAME = REPLACE(COLUMN_NAME, 'YOUR_OLD_VALUE', 'YOUR_NEW_VALUE')

With this simple tips, you can save lots of your time for this specific use case scenario.