Replace data|V4.2.1| docs|Distributed Database

Replace data

Last Updated：2023-12-25 08:49:40 Updated

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 logged on 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. For more information, see Modify user privileges.

Use the REPLACE INTO statement to replace data

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

The syntax is as follows:

REPLACE INTO table_name VALUES(list_of_values);

Parameter	Required	Description	Example
table_name	Yes	The name of the target table.	table1
(list_of_values)	Yes	The new data that replaces existing data.	(1,'CN',2001, current_timestamp ())

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 there are no conflicts with the primary key or unique key, the data is inserted.
If there are conflicts with the primary key or unique key, the existing data is removed first and then new data is 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.

Tables without records, or with records but no conflicts with the primary key or unique key constraints

For a table without records, or with records but no no conflicts with the primary key or unique key constraints, 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

Tables with records and conflicts with the primary key or unique key constraints

For a table with records and conflicts with the primary key or unique key constraints, 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 ()).