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:
REPLACE INTO table_name VALUES(list_of_values);
| Parameter | Required | Description | Examples |
|---|---|---|---|
| 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