# **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**

```text
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:

1. `INTO`: Optional keyword, indicating which table to insert data or update data.

2. `tbl_name`: The name of the table to insert or update the data.

3. `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.

4. `value`: Indicates the value to be inserted or updated. It can be a specific expression (expr) or a default value (DEFAULT).

5. `value_list`: Indicates a set of values to be inserted. Multiple values are separated by commas.

6. (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.

7. `assignment`: Represents the association of a column name and its corresponding value, used to update the form.

8. `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**

```text
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.