SQL REPLACE Statement | UnityConstruct

Reorganizing a website with a DB backend can be an utter nightmare, particularly when image folders are concerned. The thought of manually updating hundreds, or thousands, of posts is intimidating on a good day - and impossible on a bad day. Enter the SQL REPLACE statement: With a few solid search strings, one can quickly glue things back together. The effectiveness of this technique wanes with the number of adjustments made and how far down a directory tree the adjustments were made. If 100 folder moves were made at low levels, the effort with this increases. In contrast, if 1000's of objects were moved because a high level folder was moved to a different location, it might be that only 3 iterations of the REPACE will be needed. In my case there were several adjustments made, so the task was a bit laborious - HOWEVER using this replace statement saved hours of hunting down and updating tags. So here we go...

Template

update db.table_name

set table_column=REPLACE(table_column, 'old_path', 'new_path')

where table_column like '%old_path%';

Test Search String

Query first to ensure you have found a valid search string against the OLD location

select * from db.table where pagebody like '%images2/photos%';

Actual Query(minus db name)

Then make sure you have the correct NEW location and execute the update:

update db.pages

set pagebody=REPLACE(pagebody, 'images2/photos', 'images/Professional')

where pagebody like '%images2/photos%';

Test Results

Then, query for the new location to verify the changes were committed to the DB

select * from silosixphp02.pages where pagebody like '%images/Professional%';