1UPDATE users SET first_name = REPLACE (first_name, 'search', 'replace_with') where id > 0;
1UPDATE
2 table_name
3SET
4 column_name = REPLACE(column_name, 'text to find', 'text to replace with')
5WHERE
6 column_name LIKE '%text to find%';
1UPDATE products
2SET
3 productDescription = REPLACE(productDescription,
4 'abuot',
5 'about');
1The MySQL REPLACE statement is an extension to the SQL Standard. The MySQL REPLACE statement works as follows:
2
3Step 1. Insert a new row into the table, if a duplicate key error occurs.
4
5Step 2. If the insertion fails due to a duplicate-key error occurs:
6
7Delete the conflicting row that causes the duplicate key error from the table.
8Insert the new row into the table again.
9To determine whether the new row that already exists in the table, MySQL uses PRIMARY KEY or UNIQUE KEY index. If the table does not have one of these indexes, the REPLACE works like an INSERT statement.
10
11To use the REPLACE statement, you need to have at least both INSERT and DELETE privileges for the table.
12
13Notice that MySQL has the REPLACE string function which is not the REPLACE statement covered in this tutorial.
14
15The following illustrates the syntax of the REPLACE statement:
16
17REPLACE [INTO] table_name(column_list)
18VALUES(value_list);