REPLACE

Purpose

This statement is used to replace one or more records in a table. If there is no primary key or unique key conflict, it inserts the record. If there is a conflict, it deletes the conflicting record before inserting the new one.

Syntax

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 following example is based on the table definition.

obclient> CREATE TABLE product_info (product_id INT PRIMARY KEY, product_name VARCHAR(40));

1. Replace two records in the product_info table.

   obclient> REPLACE INTO product_info VALUES (1, 'Product A'),(2, 'Product B');
   

2. View the contents of the product_info table.

   obclient> SELECT * FROM product_info;
   

   The query result is as follows:

   +------------+-------------+
   | product_id | product_name|
   +------------+-------------+
   |          1 | Product A   |
   |          2 | Product B   |
   +------------+-------------+
   2 rows in set
   

3. Replace records in the product_info table, where the record with product_id=2 already exists and will be replaced.

   obclient> REPLACE INTO product_info VALUES (3, 'Product C'),(2, 'Product B Updated');
   

4. View the contents of the product_info table.

   obclient> SELECT * FROM product_info;
   

   The query result is as follows:

   +------------+------------------+
   | product_id | product_name     |
   +------------+------------------+
   |          1 | Product A        |
   |          2 | Product B Updated|
   |          3 | Product C        |
   +------------+------------------+
   3 rows in set