REPLACE

Grammar Description

REPLACE is not only a string function, but also a data operation statement for a replacement operation. The purpose of the REPLACE statement is to insert data into the table. If there is already a record that meets the criteria in the table, the record will be deleted first and then new data will be inserted. If there is no record that meets the criteria in the table, new data will be inserted directly.

REPLACE is usually used in tables with unique constraints.

The REPLACE statement requires that the table must have a primary key or a unique index to determine whether the same record already exists.
When inserting a new record using the REPLACE statement, if a record with the same primary key or unique index already exists, the old record will be deleted and the new record will be inserted, which may cause the value of the autoincrement column to change.

Grammar Structure

REPLACE
    [INTO] tbl_name
    [(col_name [, col_name] ...)]
    { VALUES(value_list)
      |
      VALUES row_constructor_list
    }

REPLACE
    [INTO] tbl_name
    SET assignment_list

value:
    {expr | DEFAULT}

value_list:
    value [, value] ...

row_constructor_list:
    ROW(value_list)

Assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Parameter definition

The REPLACE statement is used to insert data into a table or update existing data. Its syntax has two forms: one is the insertion form based on column names, and the other is the update form based on SET clause.

The following are explanations of each parameter:

INTO: Optional keyword, indicating which table to insert data or update data.
tbl_name: The name of the table to insert or update the data.
col_name: Optional parameter, indicating the column name to be inserted or updated. In the insert form, you can specify the column to be inserted by the column name; in the update form, you can specify the column to be updated.
value: Indicates the value to be inserted or updated. It can be a specific expression (expr) or a default value (DEFAULT).
value_list: Indicates a set of values to be inserted. Multiple values are separated by commas.
(Not yet supported) row_constructor_list: represents a row composed of a set of values used for insertion. The values of each line are enclosed in brackets and separated by commas.
assignment: Represents the association of a column name and its corresponding value, used to update the form.
assignment_list: represents the association of multiple column names and corresponding values, used to update the form. Multiple column names and values are separated by commas.

Note

When using the insert form, you can use the VALUES keyword followed by value_list or row_constructor_list to insert data. VALUES followed by value_list means inserting a row of data, and VALUES followed by row_constructor_list means inserting multiple rows of data.
When using the update form, use the SET keyword followed by the assignment_list to specify the column to be updated and the corresponding value.

Example

create table names(id int PRIMARY KEY,name VARCHAR(255),age int);

-- Insert a row of data, id=1, name="Abby", age=24
replace into names(id, name, age) values(1,"Abby", 24);
mysql> select name, age from names where id = 1;
+------+------+
| name | age |
+------+------+
| Abby | 24 |
+------+------+
1 row in set (0.00 sec)

mysql> select * from names;
+------+------+
| id | name | age |
+------+------+
| 1 | Abby | 24 |
+------+------+
1 row in set (0.00 sec)

-- Use the replace statement to update the name and age columns of records with id=1 to "Bob" and 25
replace into names(id, name, age) values(1,"Bobby", 25);

mysql> select name, age from names where id = 1;
+--------+------+
| name | age |
+--------+------+
| Bobby | 25 |
+--------+------+
1 row in set (0.00 sec)

mysql> select * from names;
+------+------+
| id | name | age |
+------+------+
| 1 | Bobby | 25 |
+------+------+
1 row in set (0.01 sec)

-- Use the replace statement to insert a row of data, id=2, name="Ciro", age is NULL
replace into names set id = 2, name = "Ciro";

mysql> select name, age from names where id = 2;
+------+------+
| name | age |
+------+------+
| Ciro | NULL |
+------+------+
1 row in set (0.01 sec)

mysql> select * from names;
+------+------+
| id | name | age |
+------+------+
| 1 | Bobby | 25 |
| 2 | Ciro | NULL |
+------+------+
2 rows in set (0.00 sec)

-- Use the replace statement to update the record with id=2 to the name column value of "Ciro" and the age column value is 17
replace into names set id = 2, name = "Ciro", age = 17;

mysql> select name, age from names where id = 2;
+------+------+
| name | age |
+------+------+
| Ciro | 17 |
+------+------+
1 row in set (0.01 sec)

mysql> select * from names;
+------+------+
| id | name | age |
+------+------+
| 1 | Bobby | 25 |
| 2 | Ciro | 17 |
+------+------+
2 rows in set (0.01 sec)

limit

MatrixOne currently does not support rows composed of a set of values inserted using the VALUES row_constructor_list parameter.