Tricky MySQL Update query

I walked into work and was told that lots of media file links to files that had been uploaded to the site were all broken. I quickly realized that this happened because of some reorganization I did to our file system--it was previously in a horrendous state, with files strewn all over the place.

I didn't want to have to move all the files back to where they were; I wanted to see if I could update all the pointers instead.

Of course, Drupal hasn't really given a great way to do this sort of mass-update with the content on the site. Fortunately, I discovered that all of the file uri strings were stored in a single table in the database.

Here's the tricky part: all of the uri's were of the form 'http://host.name/files/%' and I wanted them all to be of the form 'http://host.name/new/files/path/%' (replacing the specific name of the file in for %). I wanted to use part of the current value of the row's cell in the resulting value that I was going to set it to.

Here's the query I eventually came up with:

    UPDATE drupal_file_managed
    SET uri =
        CONCAT(
            'http://host.name/new/files/path/',
            SUBSTRING(
                uri,
                LENGTH('http://host.name/files/%'),
                LENGTH(uri) - LENGTH('http://host.name/files/%') + 1
            )
        )
    WHERE uri LIKE 'http://host.name/files/%'

Moral of the query: you can update just part of a row cell's contents by utilizing its current contents in the SET clause of the query. Note the subtle 1-off risks in the length parameters of the SUBSTRING function.