The REPLACE INTO statement determines whether a row exists based on a PRIMARY KEY or UNIQUE index. The statement inserts a new row if it does not exist or replaces the row if it exists in a table. We recommend that you create a PRIMARY KEY or UNIQUE index on the destination table to prevent duplicate records from being inserted into the table.
Example:
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 (0.06 sec)
obclient> REPLACE INTO t_replace values(1,'CN',2001, current_timestamp ());
Query OK, 1 row affected (0.00 sec)
obclient> REPLACE INTO t_replace
SELECT id,name,value,gmt_create FROM t_insert;
Query OK, 5 rows affected (0.00 sec)
Records: 4 Duplicates: 1 Warnings: 0
obclient> REPLACE INTO t_replace(id, name, value) values(6,'DE',20006);
Query OK, 1 row affected (0.01 sec)
obclient> select * from t_replace;
+----+------+-------+---------------------+
| id | name | value | gmt_create |
+----+------+-------+---------------------+
| 1 | CN | 10002 | 2020-04-03 18:05:45 |
| 2 | US | 10103 | 2020-04-03 18:05:54 |
| 3 | UK | 10003 | 2020-04-03 18:05:54 |
| 4 | JP | 10005 | 2020-04-03 18:06:08 |
| 6 | DE | 20006 | 2020-04-03 18:09:19 |
+----+------+-------+---------------------+
5 rows in set (0.01 sec)