Introduction

MySQL can replace part of a string across many rows with one UPDATE statement, and the workhorse is the REPLACE() string function. The key idea is simple: REPLACE(column, 'old', 'new') returns a modified value for each row, and UPDATE writes that value back only where you want the change to happen.

Using REPLACE() in an UPDATE

Suppose a table stores old URLs that need to move from http to https.

UPDATE articles
SET body = REPLACE(body, 'http://example.com', 'https://example.com')
WHERE body LIKE '%http://example.com%';

This does three things:

• scans matching rows
• computes a new string for each matching row
• writes the replaced result back to body

The WHERE clause is important. Without it, MySQL still evaluates every row in the table, even rows that do not contain the target string.

Preview the Change Before Updating

Before a mass update, it is safer to preview the before-and-after result with a SELECT.

1SELECT id,
2       body AS original_body,
3       REPLACE(body, 'http://example.com', 'https://example.com') AS updated_body
4FROM articles
5WHERE body LIKE '%http://example.com%';

This lets you verify that only the intended text will be changed.

Updating Multiple Records in One Column

REPLACE() works on each row independently, so one statement naturally covers many records. For example, renaming a company across a customer table is the same pattern:

UPDATE customers
SET company_name = REPLACE(company_name, 'Old Brand', 'New Brand')
WHERE company_name LIKE '%Old Brand%';

If a row contains the old string multiple times, REPLACE() updates all occurrences in that row.

Updating More Than One Column

If the same text must change in several columns, update them explicitly:

1UPDATE products
2SET title = REPLACE(title, '2019 Edition', '2026 Edition'),
3    description = REPLACE(description, '2019 Edition', '2026 Edition')
4WHERE title LIKE '%2019 Edition%'
5   OR description LIKE '%2019 Edition%';

Each column gets its own REPLACE() call.

Case Sensitivity and Exact Matching

REPLACE() is literal string replacement. It does not understand words, boundaries, or regular expressions. It simply looks for the exact substring.

That means details matter:

• 'cat also matches inside concatenate'
• trailing spaces count if they are present
• behavior around case sensitivity depends on the collation in use

If you need pattern-based replacement, MySQL 8 offers REGEXP_REPLACE() in supported environments. For straight substring swaps, REPLACE() is usually enough and faster to reason about.

Using Transactions Safely

Mass string replacement is the kind of change worth protecting with a transaction when your table engine supports it.

1START TRANSACTION;
2
3UPDATE articles
4SET body = REPLACE(body, 'draft.example.com', 'www.example.com')
5WHERE body LIKE '%draft.example.com%';
6
7SELECT ROW_COUNT() AS rows_changed;
8
9COMMIT;

If the preview or row count looks wrong, roll back instead of committing.

REPLACE() vs REPLACE INTO

A common source of confusion is the difference between the REPLACE() function and the REPLACE INTO statement.

• 'REPLACE() is a string function used inside expressions.'
• 'REPLACE INTO is a table-writing statement that deletes and reinserts rows based on keys.'

If your goal is substring replacement in existing text, you want the function, not REPLACE INTO.

Common Pitfalls

The most common mistake is running the update without a WHERE clause. That may still produce correct data, but it touches far more rows than necessary and increases risk.

Another issue is forgetting to preview the result first. Small string replacements can have surprisingly broad matches.

Developers also sometimes expect REPLACE() to be regex-aware. It is not. It performs literal substring substitution.

Finally, do not ignore backups or transactions when changing production data. String updates are easy to run and hard to undo if you did not plan for rollback.

Summary

• Use UPDATE ... SET column = REPLACE(column, 'old', 'new') for multi-row string replacement.
• Add a WHERE clause to limit the affected rows.
• Preview with SELECT before running the update.
• Remember that REPLACE() is a literal string function, not regex replacement.
• Do not confuse REPLACE() with the separate REPLACE INTO statement.