REPLACE

Description

This statement is used to replace one or more records in a table. If there is no primary key or unique key conflict, the records are inserted. If there is a conflict, the conflicting records are deleted before inserting the new records.

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 to replace.
column_name_list	The columns to specify the replacement data.
partition_name_list	The names of the partitions to replace in the table.

Examples

The following example is based on the following table definition.

CREATE TABLE test (c1 INT PRIMARY KEY, c2 VARCHAR(40));
Query OK, 0 rows affected (0.547 sec)

1. Replace the values of rows 1 and 2 in the test table with 'hello alibaba' and 'hello ob', respectively.

   REPLACE INTO test VALUES (1, 'hello alibaba'),(2, 'hello ob');
   Query OK, 2 rows affected (0.689 sec)
   Records: 2  Duplicates: 0  Warnings: 0

2. View the content of rows 1 and 2 in the test table.

   SELECT * FROM test;
   +----+---------------+
   | c1 | c2            |
   +----+---------------+
   |  1 | hello alibaba |
   |  2 | hello ob      |
   +----+---------------+
   2 rows in set (0.001 sec)

3. Replace the content of rows 3 and 2 in the test table with 'hello alibaba' and 'hello oceanbase', respectively.

   REPLACE INTO test VALUES (3, 'hello alibaba'),(2, 'hello oceanbase');
   Query OK, 3 rows affected (0.767 sec)
   Records: 2  Duplicates: 1  Warnings: 0

4. View the content of rows 1, 2, and 3 in the test table.

   SELECT * FROM test;
   +----+-----------------+
   | c1 | c2              |
   +----+-----------------+
   |  1 | hello alibaba   |
   |  2 | hello oceanbase |
   |  3 | hello alibaba   |
   +----+-----------------+
   3 rows in set (0.019 sec)