You can use the REPLACE INTO statement to insert or update data. This topic describes how to use this statement and provides examples.
Preparations for data replacement
Before replacing table data, confirm the following:
Ensure you are connected to the database as a user tenant. For more information about connecting to a database, see Overview of connection methods.
Note
You can query the
oceanbase.DBA_OB_TENANTSview in thesystenant to confirm the tenant mode of the current logged-in tenant.Ensure you have the
INSERT,UPDATE, andDELETEprivileges on the target table. For more information about checking user privileges, see View user privileges. If you do not have these privileges, contact the administrator to grant the privileges. For more information about granting privileges to users, see Grant privileges directly.
Replace data
The REPLACE statement is typically used to replace one or more records in a table.
The syntax for the REPLACE INTO statement is as follows:
REPLACE INTO table_name VALUES(list_of_values);
Parameter |
Required |
Description |
Example |
|---|---|---|---|
| table_name | Yes | The table into which data is to be inserted. | table1 |
| (list_of_values) | Yes | The data to be inserted. | (1,'CN',2001, current_timestamp ()) |
The REPLACE INTO statement determines whether to replace data based on the primary key or unique key of the table:
If there is no primary key or unique key conflict, the record is inserted.
If a primary key or unique key conflict exists, the existing record is deleted first, and then the new row is inserted.
Note
A table with a primary key or unique index is recommended; otherwise, duplicate records may be easily inserted.
No data records in the table, or data records exist but no primary key or unique key conflict
When the table has no data records, or has data records but no primary key or unique key conflict, you can use the REPLACE INTO statement to replace data. The result is equivalent to inserting data using the INSERT statement.
Example:
After creating the
t_replacetable, use theREPLACE INTOstatement to insert a row of data.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 setThe example shows that no data was inserted into the
t_replacetable after it was created. After executing theREPLACE INTOstatement, a record is inserted into the table.Use the
REPLACE INTOstatement again to insert a 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 setAs shown in the sample result, there is already a record in the
t_replacetable. Since the data(2,'US',2002,current_timestamp ())does not violate the uniqueness constraint with the existing records in the table, the execution result is to insert a record into thet_replacetable.Use a query statement as the
VALUESclause of theREPLACE INTOstatement to insert multiple rows of data. Insert the data from tablet_insertinto tablet_replace.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
The table contains data records and there is a primary key or unique key conflict
When the table contains data records and the inserted data violates a primary key or unique key constraint, you can use the REPLACE INTO statement to delete the conflicting data in the table and replace it with the new data.
Insert a record into the t_replace table, as shown in the following example:
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, since the id column in the t_replace table is a primary key column that must satisfy the uniqueness constraint, the inserted data (2,'EN',2002,current_timestamp ()) violates this constraint. The system deletes the original row record (2,'US',2002,current_timestamp ()) and inserts (2,'EN',2002,current_timestamp ()) as the new row record.