You can use the REPLACE INTO statement to insert or replace data. This topic describes how to use the statement.
REPLACE INTO statement
The REPLACE INTO statement determines whether a row exists based on a PRIMARY KEY or UNIQUE index.
If the target row does not exist, a new row is inserted.
If the target row exists, it is deleted and a new row 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.
Syntax:
EPLACE 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 ()) |
Examples
Use the REPLACE INTO statement if the target row does not exist
obclient> 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> REPLACE INTO t_replace values(1,'CN',2001, current_timestamp ());
Query OK, 1 row affected
obclient> SELECT * FROM t_replace;
+----+------+-------+---------------------+
id name value gmt_create
+----+------+-------+---------------------+
1 CN 2001 2022-03-22 16:13:55
+----+------+-------+---------------------+
1 row in set
Use a query statement as the VALUES clause of REPLACE INTO if the target row exists
obclient> SELECT * FROM t_insert;
+----+------+-------+---------------------+
id name value gmt_create
+----+------+-------+---------------------+
1 CN 10001 1970-01-01 16:15:17
2 US 10002 1970-01-01 16:15:27
3 EN 10003 1970-01-01 16:15:27
4 JP 10004 1970-01-01 16:15:41
+----+------+-------+---------------------+
4 rows in set
obclient> REPLACE INTO t_replace
SELECT id,name,value,gmt_create FROM t_insert;
Query OK, 5 rows affected
Records: 4 Duplicates: 1 Warnings: 0
obclient> SELECT * FROM t_replace;
+----+------+-------+---------------------+
id name value gmt_create
+----+------+-------+---------------------+
1 CN 10001 1970-01-01 16:15:17
2 US 10002 1970-01-01 16:15:27
3 EN 10003 1970-01-01 16:15:27
4 JP 10004 1970-01-01 16:15:41
+----+------+-------+---------------------+
4 rows in set
Use the REPLACE INTO statement for a NOT NULL column with a default value
In the following example, the gmt_create column in the t_replace table is a NOT NULL column that has a default value. Therefore, the gmt_create parameter can be omitted in the VALUES clause.
obclient> REPLACE INTO t_replace(id, name, value) values(6,'DE',20006);
Query OK, 1 row affected
obclient> SELECT * FROM t_replace;
+----+------+-------+---------------------+
id name value gmt_create
+----+------+-------+---------------------+
1 CN 10002 1970-01-01 18:05:45
2 US 10103 1970-01-01 18:05:54
3 UK 10003 1970-01-01 18:05:54
4 JP 10005 1970-01-01 18:06:08
6 DE 20006 1970-01-01 18:09:19
+----+------+-------+---------------------+
5 rows in set