mobile logo

MySQL query to find and replace urls that point to the root of a directory but not a subdirectory underneath it

Solution:

You can find rows that match or don’t match using regexp and update based on that conditions.

update wp_posts
  set post_content = replace(post_content, 'oldpath', 'newpath')
  where post_content not regexp 'wp-content/uploads/.*/.*'

It’s a simplistic regexp but it should get the job done.

mysql> select 'wp-content/uploads/1.jpg' not regexp 'wp-content/uploads/.*/.*';
+------------------------------------------------------------------+
| 'wp-content/uploads/1.jpg' not regexp 'wp-content/uploads/.*/.*' |
+------------------------------------------------------------------+
|                                                                1 |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select 'wp-content/uploads/2014/1.jpg' not regexp 'wp-content/uploads/.*/.*';
+-----------------------------------------------------------------------+
| 'wp-content/uploads/2014/1.jpg' not regexp 'wp-content/uploads/.*/.*' |
+-----------------------------------------------------------------------+
|                                                                     0 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

updated regex to be more exclusive
change 'wp-content/uploads/.*/.*'; to 'wp-content/uploads/[^/]*/[^"]*"'; and it will work in the fiddle you’ve posted below, see updated version here: http://sqlfiddle.com/#!9/d636b/1 (please take backups before executing this of course)