Description

When we need to replace a specific word with another word on a specific column across all or selected rows of a table, we need to use an UPDATE query as explained in this example.

  • An UPDATE query to update all or selected rows (based on WHERE condition) of a table to replace the old value with the new value on a specific column.

Let's assume a table with the below table definition and data, where we need to update the department 'AAA' to 'BB' against all the rows of a table.

ID     Name     Department
===========================
1    Arun       AAA001
2    Kiran      AAA002
3    James      CC001
4    Sammy      AA001
5    Martin     AAA003

After the update, the table data must look like the one below.

ID     Name     Department
===========================
1    Arun       BB001
2    Kiran      BB002
3    James      CC001
4    Sammy      AA001
5    Martin     BB003

Syntax

UPDATE tableName SET columnName = REPLACE(columnName, oldValue, newValue);

Examples

Use the below SQL Query to apply the update to all the rows of a table.

UPDATE employees
SET department = REPLACE(department, 'AAA', 'BB');

Use the below SQL Query to apply the update to rows selected based on the WHERE condition.

UPDATE employees
SET department = REPLACE(department, 'AAA', 'BB')
WHERE department like 'AAA%';

Overall

We now know the SQL Query to find and replace a specific word in a specific column of a table.