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-compatible 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 determines the operation based on whether the data to be replaced conflicts with the primary key or unique key of the table:

• If no primary key or unique key conflict exists, the record is inserted.

• If a primary key or unique key conflict exists, the existing record is deleted first, and then the new record is inserted.

  Note

  We recommend that the target table has a primary key or unique index; otherwise, duplicate records may be inserted.

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 as the VALUES clause of the REPLACE INTO statement to insert multiple rows of data. Insert the data from 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 delete the existing conflicting records in the table and replace them with 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