MySQL: Updating Table using UPDATE REPLACE Query

This query is quite helpful when you moving from development machine to production where your table content is full of dozen 'http://localhost/' links.

Syntax

UPDATE [table] SET [column] = REPLACE(
  [column],
  [old_value],
  [new_value]
) WHERE [column] LIKE [old_value];

Example

Here is example updating one of OpenCart column. op_information_description is column used to save articles (or opencart call it information).

UPDATE op_information_description SET description = REPLACE(
  description,
  'http://localhost/shop/',
  'http://store.example.com/'
) WHERE '%http://localhost/shop/%';

Share on Facebook Twitter