Earlier I set up a development WordPress site that mirrored a live WordPress installation. To do this I dumped the entire WordPress database using mysqldump and then imported it into a new database with mysql. Among other things that needed to be changed I needed to update any links that referenced the live site’s URL with the development site’s URL. Below I provide the command to make such an update to only a matching portion of a column in row located in a MySQL database.
Use The MySQL Update Command To Modify Only The Matched Part Of A Column:
Here is the syntax to modify only text that you match using update, set, and replace. Make sure you backup your data before you run a command like this in case anything goes wrong you can immediately restore the backup and figure out the issue afterwards. I might also suggest doing this in a development database instead of on a live server first.
SQL Update Syntax To Replace Text In A Column:
- update wp_posts SET post_content = REPLACE ( `post_content` , 'www.example.com', 'newurl.example.com' );
The above will loop through every row in the wp_posts table and replace www.example.com with newurl.example.com. It will not modify anything except for what is included in the single quotes so make sure to not accidentally add an extra space in any of the variables the command takes. Running this command should return results similar to the below.
Example SQL Update Command That Replaces Text In A Column:
- mysql> update qd_db_posts SET post_content = REPLACE ( `post_content` , 'www.example.com', 'newurl.example.com' );
- Query OK, 1001 rows affected (3.36 sec)
- Rows matched: 3905 Changed: 1001 Warnings: 0
Notice that 1001 of the 3905 rows in the wp_posts table were modified. I suggest actually getting a count on the number of rows in the table before running the command using something like the below which will tell you total rows and rows with the text you are matching.
SQL Select Commands To Verify Row Total Count & Matching Rows:
- mysql> select count(*) from wp_posts where post_content LIKE '%www.example.com%';
- +----------+
- | count(*) |
- +----------+
- | 1021 |
- +----------+
- 1 row in set (0.21 sec)
- mysql> select count(*) from wp_posts;
- +----------+
- | count(*) |
- +----------+
- | 3905 |
- +----------+
- 1 row in set (0.00 sec)
- mysql>
That is it. If you made any mistakes simply restore from the backup you made and give it another go once you figure out whats wrong.
