Summary: in this tutorial, you will learn how to use the MySQL REPLACE statement to insert or update data in database tables.
Introduction to MySQL REPLACE statement
REPLACE statement is a MySQL extension to the SQL standard. The MySQL
REPLACE statement works like the INSERT statement with the additional rules:
- If the record which you want to insert does not exist, the MySQL
REPLACEinserts a new record.
- If the record which you want to insert already exists, MySQL
REPLACEdeletes the old record first and then insert a new record.
In order to use MySQL
REPLACE statement, you need to have at least both
Please don’t confuse the
REPLACE statement with the REPLACE string function.
MySQL REPLACE statements
MySQL REPLACE INTO statement
The first form of the
REPLACE statement is similar to the
INSERT statement except the keyword
INSERT is replaced by the
REPLACE keyword as follows:
REPLACE INTO table_name(column_name1,column_name2,…) VALUES(value1,value2,…)
officestable, you use the following query:
REPLACE INTO offices(officecode,city) VALUES(8,'San Jose')
Notice that the default values of of the columns that does not appear in the
REPLACE statement will be inserted to the corresponding columns.
If you want to update the office that we have inserted with the new city
San Mateo, you can use the
REPLACE statement as follows:
REPLACE INTO offices(officecode,city) VALUES(8,'San Mateo')
Two rows affected by the query above because the existing record was deleted and the new one was inserted.
MySQL REPLACE acts like UPDATE statement
The second form of MySQL
REPLACE like the
UPDATE statement as follows:
REPLACE INTO table_name SET column_name1 = value1 AND column2 = value2
Notice that there is no WHERE clause in the
REPLACE statement. For example, if you want to update the office in
San Mateocity with
8, you use the
REPLACE statement as follows:
REPLACE INTO offices SET officecode = 8 and city = 'Santa Cruz'
MySQL REPLACE INTO with SELECT statement
The third form of
REPLACE is similar to INSERT INTO SELECT statement:
REPLACE INTO table_name1(column_name1,column_name2,…) SELECT column_name1, column_name2… FROM table_name2 WHERE where_condition
Suppose if you want to copy the
officecode value 1, you use the
REPLACE INTO SELECT statement as the following query:
REPLACE INTO offices(officecode, city, phone, addressline1, addressline2, state, country, postalcode, territory) SELECT (SELECT MAX(officecode) + 1 FROM offices), city, phone, addressline1, addressline2, state, country, postalcode, territory FROM offices WHERE officecode = 1
MySQL REPLACE usages
There are several important points you need to know when you use the
- If you are developing an application that potentially supports not only MySQL database, try to avoid using the
REPLACEstatement because other database management systems may not support the
REPLACEstatement. Instead, you can use the combination of the INSERT and DELETE statements.
- If you are using the
REPLACEstatement in the table that has triggers and if the deletion of duplicate key happens, the triggers will be fired in the following sequence:
- You should use the UPDATE statement in case you want to update data because it performs faster than the
In this tutorial, you’ve learned different forms of MySQL REPLACE statement to insert or update data in database tables.