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
The MySQL REPLACE
statement is an extension to the SQL Standard. The MySQL REPLACE
statement works as follows:
Step 1. Insert a new row into the table, if a duplicate key error occurs.
Step 2. If the insertion fails due to a duplicate-key error occurs:
- Delete the conflicting row that causes the duplicate key error from the table.
- Insert the new row into the table again.
To 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
statement works like an INSERT
statement.
To use the REPLACE
statement, you need to have at least both INSERT
and DELETE
privileges for the table.
Notice that MySQL has the REPLACE
string function which is not the REPLACE
statement covered in this tutorial.
Using MySQL REPLACE to insert a new row
The following illustrates the syntax of the REPLACE
statement:
REPLACE [INTO] table_name(column_list)
VALUES(value_list);
Code language: SQL (Structured Query Language) (sql)
It is similar to the INSERT
statement except for the keyword REPLACE
. Let’s take a look at the following example of using the REPLACE
statement to see how it works.
First, create a new table named cities
as follows:
CREATE TABLE cities (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
population INT NOT NULL
);
Code language: SQL (Structured Query Language) (sql)
Next, insert some rows into the cities
table:
INSERT INTO cities(name,population)
VALUES('New York',8008278),
('Los Angeles',3694825),
('San Diego',1223405);
Code language: SQL (Structured Query Language) (sql)
Then, query data from the cities
table to verify the insert operation.
SELECT * FROM cities;
Code language: SQL (Structured Query Language) (sql)
After that, use the REPLACE
statement to update the population of the Los Angeles
city to 3696820
.
REPLACE INTO cities(id,population)
VALUES(2,3696820);
Code language: SQL (Structured Query Language) (sql)
Finally, query the data of the cities
table again to verify the replacement.
SELECT * FROM cities;
Code language: SQL (Structured Query Language) (sql)
The value of the name
column is NULL
now.
The REPLACE
statement works as follows:
- First, the
REPLACE
statement attempted to insert a new row intocities
the table. The insertion failed because the id 2 already exists in thecities
table. - Then, the
REPLACE
statement deleted the row with id 2 and inserted a new row with the same id 2 and population3696820
. Because no value is specified for the name column, it was set toNULL
.
Using MySQL REPLACE statement to update a row
The following illustrates how to use the REPLACE
statement to update data:
REPLACE INTO table
SET column1 = value1,
column2 = value2;
Code language: SQL (Structured Query Language) (sql)
This statement is like the UPDATE
statement except for the REPLACE
keyword. In addition, it has no WHERE
clause.
This example uses the REPLACE
statement to update the population of the city Phoenix
to 1768980
:
REPLACE INTO cities
SET id = 4,
name = 'Phoenix',
population = 1768980;
Code language: SQL (Structured Query Language) (sql)
Unlike the UPDATE
statement, if you don’t specify the value for the column in the SET
clause, the REPLACE
statement will use the default value of that column.
SELECT * FROM cities;
Code language: SQL (Structured Query Language) (sql)
Using MySQL REPLACE to insert data from a SELECT statement
The following illustrates the REPLACE
statement that inserts data into a table with the data coming from a query.
REPLACE INTO table_1(column_list)
SELECT column_list
FROM table_2
WHERE where_condition;
Code language: SQL (Structured Query Language) (sql)
Note that this form of the REPLACE
statement is similar to INSERT INTO SELECT
statement.
The following statement uses the REPLACE INTO
statement to copy a row within the same table:
REPLACE INTO
cities(name,population)
SELECT
name,
population
FROM
cities
WHERE id = 1;
Code language: SQL (Structured Query Language) (sql)
In this tutorial, you’ve learned different forms of the MySQL REPLACE
statement to insert or update data in a table.