Replace data

You can use the REPLACE INTO statement to insert or update data. This topic describes how to use the statement.

Prerequisites

Before you replace data in a table, make sure that:

• You have connected to a MySQL tenant of OceanBase Database. For more information about how to connect to OceanBase Database, see Connection methods.

  Note

  You can query the oceanbase.DBA_OB_TENANTS view in the sys tenant to confirm the mode of the tenant to which you have logged on.

• You have the INSERT, UPDATE, and DELETE privileges on the target table. For more information about how to view your privileges, see View user privileges. If you do not have the required privileges, contact the administrator to obtain the privileges. For more information, see Grant direct privileges.

Replace data

Generally, the REPLACE INTO statement is used to replace one or more records in a table.

The syntax of the REPLACE INTO statement is as follows:

REPLACE INTO table_name VALUES(list_of_values);

The REPLACE INTO statement will determine whether to directly insert new data or use new data to update existing data based on whether conflicts exist.

• If the data to be inserted does not conflict with the values of the primary key or unique key column, the data is inserted.

• If the data to be inserted conflicts with the values of the primary key or unique key column, the existing records are deleted and then new records are inserted.

  Note

  We recommend that you create a PRIMARY KEY or UNIQUE index on the target table to avoid inserting duplicate records into the table.

Replace data in a table without records or with records but no primary key or unique key conflicts

For a table without records or with records but no primary key or unique key conflicts, you can use the REPLACE INTO statement to insert data into the table. The result set is the same as that obtained by using the INSERT statement to insert data.

Here is an example:

• Create a table named t_replace and then use the REPLACE INTO statement to insert a row of data into the table.

  obclient [test]> CREATE TABLE t_replace(
      id number NOT NULL PRIMARY KEY
      , name varchar(10) NOT NULL
      , value number
      ,gmt_create timestamp NOT NULL DEFAULT current_timestamp
  );
  Query OK, 0 rows affected
  
  obclient [test]> REPLACE INTO t_replace values(1,'CN',2001, current_timestamp ());
  Query OK, 1 row affected
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  +----+------+-------+---------------------+
  1 row in set
  

  In this example, no data is inserted after the t_replace table is created. One record is inserted into the table after the REPLACE INTO statement is executed.

• Execute the REPLACE INTO statement again to insert another row of data.

  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  +----+------+-------+---------------------+
  1 row in set
  
  obclient [test]> REPLACE INTO t_replace(id, name, value, gmt_create) VALUES(2,'US',2002,current_timestamp ());
  Query OK, 1 row affected
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  |  2 | US   |  2002 | 2022-10-13 14:17:56 |
  +----+------+-------+---------------------+
  2 rows in set
  

  In this example, the t_replace table already has a record. The (2,'US',2002,current_timestamp ()) data record is not duplicate with the record in the table and therefore does not violate the UNIQUE constraint and is inserted into the t_replace table.

• Use a query statement to replace the VALUES clause in the REPLACE INTO statement to insert multiple rows of data. The following statement inserts the data of the t_insert table into the t_replace table.

  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  |  2 | US   |  2002 | 2022-10-13 14:17:56 |
  +----+------+-------+---------------------+
  2 rows in set
  
  obclient [test]> SELECT * FROM t_insert;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  7 | EN   |  1007 | 2022-10-13 14:36:36 |
  |  8 | JP   |  1008 | 2022-10-13 14:36:36 |
  +----+------+-------+---------------------+
  2 rows in set
  
  obclient [test]> REPLACE INTO t_replace
       SELECT id,name,value,gmt_create FROM t_insert;
  Query OK, 2 rows affected
  Records: 2  Duplicates: 0  Warnings: 0
  
  obclient [test]> SELECT * FROM t_replace;
  +----+------+-------+---------------------+
  | id | name | value | gmt_create          |
  +----+------+-------+---------------------+
  |  1 | CN   |  2001 | 2022-10-13 14:06:58 |
  |  2 | US   |  2002 | 2022-10-13 14:17:56 |
  |  7 | EN   |  1007 | 2022-10-13 14:36:36 |
  |  8 | JP   |  1008 | 2022-10-13 14:36:36 |
  +----+------+-------+---------------------+
  4 rows in set
  

Replace data in a table with records and primary key or unique key conflicts

For a table with records and primary key or unique key conflicts, you can use the REPLACE INTO statement to update the conflicting data in the table to the new data.

For example, insert a record into the t_replace table:

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-10-13 14:06:58 |
|  2 | US   |  2002 | 2022-10-13 14:17:56 |
|  7 | EN   |  1007 | 2022-10-13 14:36:36 |
|  8 | JP   |  1008 | 2022-10-13 14:36:36 |
+----+------+-------+---------------------+
4 rows in set

obclient [test]> REPLACE INTO t_replace(id, name, value, gmt_create) VALUES(2,'EN',2002,current_timestamp ());
Query OK, 2 rows affected

obclient [test]> SELECT * FROM t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create          |
+----+------+-------+---------------------+
|  1 | CN   |  2001 | 2022-10-13 14:06:58 |
|  2 | EN   |  2002 | 2022-10-13 14:44:33 |
|  7 | EN   |  1007 | 2022-10-13 14:36:36 |
|  8 | JP   |  1008 | 2022-10-13 14:36:36 |
+----+------+-------+---------------------+
4 rows in set

In this example, the id column in the t_replace table is the primary key column and must meet the UNIQUE constraint. The inserted record (2,'EN',2002,current_timestamp ()) violates the UNIQUE constraint. Therefore, the system deletes the original record (2,'US',2002,current_timestamp ()) and inserts the new record (2,'EN',2002,current_timestamp ()).

References

• Insert data

• Update data

• Delete data