REPLACE

Purpose

You can use this statement to replace one or more records in a table. If no primary key or unique key conflict exists, records are directly inserted. If a conflict exists, the conflicting records are deleted. Then, the new records are 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

Examples

The table used in the examples is defined as follows:

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

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

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

2. Query the data in row 1 and row 2 of table test.

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

3. Replace the values in row 3 and row 2 of table test with 'hello alibaba' and hello 'oceanbase' respectively.

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

4. Query the data in row 1, row 2, and row 3 of table test.

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