MySQL REPLACE?

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 a MySQL extension to the standard SQL. The MySQL REPLACEstatement works as follows:

• If the new row already does not exist, the MySQL REPLACE  statement inserts a new row.
• If the new row already exist, the REPLACE  statement deletes the old row first and then inserts a new row. In some cases, the REPLACE statement updates the existing row only.

To determine whether the new row 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 is equivalent to the INSERT statement.

To use the MySQL REPLACE statement, you need to have at least both INSERT and DELETE privileges.

Notice that there is a REPLACE string function which is not the REPLACE statement covered in this tutorial.

MySQL REPLACE statement example

Let’s take a look at an example of using the REPLACE statement to have a better understanding of how it works.

First, create a new table named cities as follows:

1 2 3 4 5

CREATE TABLE cities (     id INT AUTO_INCREMENT PRIMARY KEY,     name VARCHAR(50),     population INT NOT NULL );

Next, insert some rows into the cities table:

1 2 3 4

INSERT INTO cities(name,population) VALUES('New York',8008278),   ('Los Angeles',3694825),   ('San Diego',1223405);

We query data from the cities table to verify the insert operation.

1 2 3 4

SELECT     * FROM     cities;

We have three cities in the cities table.

Then, suppose we want to update the population of the New York city to 1008256. We can use the UPDATE statement as follows:

1 2 3 4 5

UPDATE cities SET     population = 1008256 WHERE     id = 1;

We query the data from the cities table again to verify the update.

1 2 3 4

SELECT     * FROM     cities;

The UPDATE statement updated the data as expected.

After that, use the REPLACE statement to update the population of the Los Angeles city to 3696820.

1 2	REPLACE INTO cities(id,population) VALUES(2,3696820);

Finally, query the data of the cities table again to verify the replacement.

1 2 3 4

SELECT     * FROM     cities;

The name column is NULL now. You may expect that the value of the name column remains intact. However, the REPLACE statement does not behave this way. In this case, the REPLACE statement works as follows:

1. The REPLACE statement first inserts the new row into the cities table with the information provided by the column list. The insertion fails because the row with id 2 already exists in the cities table, therefore, MySQL raises a duplicate-key error.
2. The REPLACE statement then updates the row that has the key specified in the value of the idcolumn. In the normal process, it would delete the old row with conflict id first and then inserts a new row.

We know that the REPLACE statement did not delete the old row and inserted the new row because the value of the id column is 2 instead of 4.

MySQL REPLACE and INSERT

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:

1 2	REPLACE INTO table_name(column_list) VALUES(value_list);

For example, if you want to insert a new row into the cities table, you use the following query:

1 2	REPLACE INTO cities(name,population) VALUES('Phoenix',1321523);

Notice that the default values of the columns that do not appear in the REPLACE statement will be inserted into the corresponding columns. In case the column that has the NOT NULL attribute and does not have a default value, and you don’t specify the value in the REPLACE statement, MySQL will raise an error. This is a difference between the REPLACE and INSERT statements.

For example, in the following statement, we specify only the value for the name column, not the population column. MySQL raises an error message. Because the population column does not accept a NULL value and we did not specify a default value for it when we defined the cities table.

1 2	REPLACE INTO cities(name) VALUES('Houston');

This is the error message that MySQL issued:

1	Error Code: 1364. Field 'population' doesn't have a default value

MySQL REPLACE and UPDATE

The second form of REPLACE statement is similar to the UPDATE statement as follows:

1 2 3

REPLACE INTO table SET column1 = value1,     column2 = value2;

Notice that there is no WHERE clause in the REPLACE statement.

For example, if you want to update the population of the Phoenix city to 1768980, you use the REPLACE statement as follows:

1 2 3 4

REPLACE INTO cities SET id = 4,     name = 'Phoenix',     population = 1768980;

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.

1 2 3 4

SELECT     * FROM     cities;

MySQL REPLACE INTO and SELECT

The third form of  REPLACE statement is similar to INSERT INTO SELECT statement:

1 2 3 4

REPLACE INTO table_1(column_list) SELECT column_list FROM table_2 WHERE where_condition;

Suppose, you want to copy the city with id value 1, you use the REPLACE INTO SELECT statement as the following query:

1 2 3

REPLACE INTO cities(name,population) SELECT name,population FROM cities WHERE id = 1;

MySQL REPLACE  statement usages

There are several important points you need to know when you use the REPLACE statement:

• If you develop an application that supports not only MySQL database but also other relational database management systems (RDBMS), you should avoid using the REPLACE statement because other RDBMS may not support it. Instead, you can use the combination of the DELETE and INSERT statements within a transaction.
• If you are using the REPLACE statement in the table that has triggers and the deletion of duplicate-key error occurs, the triggers will be fired in the following sequence:BEFORE INSERT BEFORE DELETE , AFTER DELETE , AFTER INSERT  in case the REPLACE statement deletes current row and inserts the new row. In case the REPLACE statement updates the current row, the BEFORE UPDATEand AFTER UPDATE triggers are fired.

In this tutorial, you’ve learned different forms of REPLACE statement to insert or update data in tables.