Updating embedded content from the database

This is part 2 (or a re-visit) of a previous blog post (Drupal Post Migration Data Cleanup) on updating content after moving it to another domain . If you have your data inside a multisite, you probably have some embedded some images/files into node bodies (or such like) and want an easy way to replace all of the instances. OK great. Using the REPLACE function in MySql you can easily replace instances where the url for the files is wrong. Brilliant.

UPDATE `field_data_body`
SET `body_value` = REPLACE(`body_value`, "sites/$new_url/files/", "sites/example.com/files/");
UPDATE `field_revision_body`
SET `body_value` = REPLACE(`body_value`, "sites/$new_url/files/", "sites/example.com/files/");

The example above would move all links to the data from the sites/$new_url/files directory to the sites/example.com/files directory. You can also run this from the command line, providing you have drush running using drush sqlq and then the query. Clear your caches (as always) and you should be away!

Happy Drupalling.