There are times when you have to search and replace a substring for all values of a string in a column,this can be easily done by combining UPDATE statement with a SELECT statement to dynamically update values depending on the existing value in the row.

UPDATE table_name,
 (SELECT id, CONCAT('prefix',REPLACE(col_name,'postfix','')) as name
  FROM table_name
  WHERE id BETWEEN 101 AND 1000) as sub
SET col_name = sub.name
WHERE table_name.id = sub.id
  AND table_name.id BETWEEN 101 AND 1000;

SELECT statement is used in a sub-query to workaround the following error: ’#1093 - You can’t specify target table ’table_name’ for update in FROM clause'

As MySQL does not allow table to be modified is directly used for SELECT in the UPDATE statement[1].

References:

1. stackoverflow.com