SQL Query – Change siteurl in wp_options Table For All Sites in WP Multisite

These SQL queries let you change the value for “siteurl” option in the wp_options table in the WordPress database for all sites in a WP Multisite network. So, each line changes the siteurl in the wp_options table for one site in the network.

The first line changes it for the main site in your Multisite network, which is in the “wp_options” table.

The next line changes it for the second site in your Multisite network, which is in the “wp_2_options” table. The next line changes it for the third site, which is in the “wp_3_options” table. This example goes all the way up to the “wp_8_options” table, which would be the eighth site on your network.

If you have more than eight sites, then add lines as necessary. If you have less than eight sites, delete lines that are not needed in your case. For each additional site, add a new line and change “wp_8_options” to “wp_9_options”, and increment the number accordingly. Note that “wp_#_options” appears twice on each line, so make the change in both places on each extra line that you add.

This is useful when you need to change your site from HTTP to HTTPS (after acquiring an SSL certificate). Manually, you would have go in to the admin area of each of your multisite network sites, and going to the settings page to update the “Site Address (URL)” for each site in your multisite network. Rather than do that, you could just once log in to your WordPress database (in PHPMyAdmin), and run these queries to update the “Site Address (URL).”

Mandatory step: On every line, change http://old_url.com to your own URL which you want to change. Also, change https://new_url.com to the new URL that you want.

UPDATE `wp_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_options`.`option_name` = 'siteurl';

UPDATE `wp_2_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_2_options`.`option_name` = 'siteurl';

UPDATE `wp_3_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_3_options`.`option_name` = 'siteurl';

UPDATE `wp_4_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_4_options`.`option_name` = 'siteurl';

UPDATE `wp_5_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_5_options`.`option_name` = 'siteurl';

UPDATE `wp_6_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_6_options`.`option_name` = 'siteurl';

UPDATE `wp_7_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_7_options`.`option_name` = 'siteurl';

UPDATE `wp_8_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_8_options`.`option_name` = 'siteurl';

If you also want to update the “WordPress Address (URL)”, use the following SQL queries.

If you have more than eight sites, then add lines as necessary. If you have less than eight sites, delete lines that are not needed in your case. For each additional site, add a new line and change “wp_8_options” to “wp_9_options”, and increment the number accordingly. Note that “wp_#_options” appears twice on each line, so make the change in both places on each extra line that you add.

Mandatory step: On every line, change http://old_url.com to your own URL which you want to change. Also, change https://new_url.com to the new URL that you want.

UPDATE `wp_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_options`.`option_name` = 'home';

UPDATE `wp_2_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_2_options`.`option_name` = 'home';

UPDATE `wp_3_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_3_options`.`option_name` = 'home';

UPDATE `wp_4_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_4_options`.`option_name` = 'home';

UPDATE `wp_5_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_5_options`.`option_name` = 'home';

UPDATE `wp_6_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_6_options`.`option_name` = 'home';

UPDATE `wp_7_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_7_options`.`option_name` = 'home';

UPDATE `wp_8_options` SET `option_value` = replace( option_value, "http://old_url.com", "https://new_url.com" ) WHERE `wp_8_options`.`option_name` = 'home';