MySQL REPLACE

In MySQL, REPLACE is like INSERT. Both REPLACE and INSERT can insert rows into a table, but there is a difference between them. If the old row in the table has the same value for a PRIMARY KEY or UNIQUE index, the old row is deleted before the new row is inserted.

The REPLACE statement is not defined in SQL standard, it is a MySQL extension to the SQL standard.

REPLACE syntax

You can use a REPLACE statement to insert one or more rows of data. This is the syntax of the REPLACE statement:

REPLACE [INTO] table_name (column_1, column_2, ...)
VALUES (value_11, value_12, ...),
       (value_21, value_22, ...)
       ...;

Here:

REPLACE INTO and VALUES are keywords, and INTO can be omitted.
You can specify the table name after REPLACE INTO keyword.
You can specify a list of column names which columns you want to insert value after the table name.
You can specify the new rows after VALUES keyword. When inserting multiple rows, you should use commas to separate multiple rows.

The REPLACE statement and the INSERT statement are similar.

In the REPLACE statement, you can also use SET keyword and this only used to insert one row into a table. Here is the syntax:

REPLACE [INTO] table_name
SET column1 = value1,
    column2 = value2,
    ...;

This is similar to UPDATE, but there is a difference between them. UPDATE only update the value of the specified columns, but REPLACE will delete the old row and insert the new row.

To use REPLACE, the user must have INSERT and DELETE privileges on the table.

REPLACE examples

To demonstrate the usage of the REPLACE statement, we need to create a table named user using CREATE TABLE statement:

CREATE TABLE user (
    id INT,
    name VARCHAR(255) NOT NULL,
    age INT,
    PRIMARY KEY (id)
);

Here, we create user table that contains three columns: id, name and age, and the id is the primary key column.

Insert one row

Let us use the following statement to insert one new row into the user table:

REPLACE INTO user (id, name, age)
VALUES (1, "Jim", 18);

Query OK, 1 row affected (0.00 sec)

The output 1 row affected indicates that one row has been inserted successfully. We can also verify the result by querying:

SELECT * FROM user;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jim  |   18 |
+----+------+------+
1 row in set (0.01 sec)

Insert multiple rows

Let us use the following statement to insert two new rows into the user table:

REPLACE INTO user (id, name, age)
VALUES (2, "Tim", 19), (3, "Lucy", 16);

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

The output 2 row affected Indicates that 2 rows have been inserted successfully. Let’s take a look at rows in the table.

SELECT * FROM user;

+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Jim  |   18 |
|  2 | Tim  |   19 |
|  3 | Lucy |   16 |
+----+------+------+
3 rows in set (0.00 sec)

Replace one row

Let’s replace one row in the table using the following statement:

REPLACE INTO user (id, name, age)
VALUES (3, "Alice", 18);

Query OK, 2 rows affected (0.01 sec)

In this example, we want to insert a row which id value is 3. But the user table contains a row which id value is 3.

The output here is 2 rows affected, it means that one old row has been deleted and one new row has been inserted.

Let’s take a look at rows in the table.

SELECT * FROM user;

+----+-------+------+
| id | name  | age  |
+----+-------+------+
|  1 | Jim   |   18 |
|  2 | Tim   |   19 |
|  3 | Alice |   18 |
+----+-------+------+
3 rows in set (0.00 sec)

Replace and insert

Look at the follow statment:

REPLACE INTO user (id, name, age)
VALUES (2, "James", 20), (4, "Kobe", 16);

Query OK, 3 rows affected (0.00 sec)
Records: 2  Duplicates: 1  Warnings: 0

In this example,

3 rows affected indicates that one ole row has been deleted and two new rows have been inserted.
Records: 2 indicates that there are two rows in the statement.
Duplicates: 1 indicates that one row has the same primary key value.