Description
You can execute the REPLACE statement to replace one or more records in a table. If an old record has the same value as a new record in a primary or unique key column, the old record is deleted before the new record is inserted.
Syntax
replace_stmt:
REPLACE [INTO] table_factor [PARTITION (partition_name_list)] [(column_name_list)]
{VALUES | VALUE} column_value_lists;
partition_name_list:
partition_name [, partition_name ...]
column_name_list:
column_name [, column_name ...]
column_value_lists:
(column_value_list) [, (column_value_list) ...]
column_value_list:
column_value [, column_value ...]
column_value:
{expression | DEFAULT}
Parameters
| Parameter | Description |
|---|---|
| table_factor | The name of the table in which you want to replace records. |
| column_name_list | The columns in which you want to replace data. |
| partition_name_list | The table partitions in which you want to replace records. |
Examples
The following statement defines a table named test. All the examples in this topic are based on the test table.
OceanBase(admin@test)>create table test (c1 int primary key, c2 varchar(40));
Query OK, 0 rows affected (0.23 sec)
- Replace the value in row 1 in the test table with 'hello alibaba' and replace the value in row 2 with 'hello ob'.
OceanBase(admin@test)>REPLACE INTO test VALUES (1, 'hello alibaba'),(2, 'hello ob');
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
- Query row 1 and row 2 in the test table.
OceanBase(admin@test)>SELECT * FROM test;
+----+---------------+
| c1 | c2 |
+----+---------------+
| 1 | hello alibaba |
| 2 | hello ob |
+----+---------------+
2 rows in set (0.00 sec)
- Replace the value in row 3 in the test table with 'hello alibaba' and replace the value in row 2 with 'hello oceanbase'.
OceanBase(admin@test)>REPLACE INTO test VALUES (3, 'hello alibaba'),(2, 'hello oceanbase');
Query OK, 3 rows affected (0.00 sec)
Records: 2 Duplicates: 1 Warnings: 0
- Query row 1, row 2, and row 3 in the test table.
OceanBase(admin@test)>SELECT * FROM test;
+----+-----------------+
| c1 | c2 |
+----+-----------------+
| 1 | hello alibaba |
| 2 | hello oceanbase |
| 3 | hello alibaba |
+----+-----------------+
3 rows in set (0.00 sec)
