You can use the MERGE INTO statement to insert records to or update records in a table.
Prerequisites
Before you replace data in a table, make sure that:
You have connected to an Oracle 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_TENANTSview in thesystenant to confirm the mode of the tenant to which you have logged on.You have the
SELECTobject privilege on the source table, and theINSERTandUPDATEprivileges on the destination 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 to obtain the privileges. For more information, see Grant direct privileges.
Replace data in a table
Use the MERGE INTO statement to replace one or more records in a table.
The syntax is as follows:
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col1_val,
col2 = col2_val
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
Parameter |
Required? |
Description |
|---|---|---|
| MERGE INTO table_name alias1 | Yes | The name and alias of the table into which data is to be inserted. |
| USING (table|view|sub_query) alias2 | Yes | The source table (view, subquery) and its alias. |
| ON (join condition) | No | The judgment condition. |
| WHEN MATCHED THEN UPDATE table_name SET col1 = col_val1,col2 = col2_val | Yes | Specifies to execute the UPDATE statement when the specified condition is matched. |
| WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); | Yes | Specifies to execute the INSERT statement when the specified condition is not matched. |
When you use a MERGE INTO statement to replace data, if the record in the source table does not exist in the destination table, data is inserted into the destination table. Otherwise, the existing record in the destination table is updated.
Records in the source table do not exist in the destination table
If the records in the source table do not exist in the destination table, you can use the REPLACE INTO statement to insert these records into the destination table.
The information about the source t_merge table is as follows:
obclient [SYS]> CREATE TABLE t_merge(
id number NOT NULL PRIMARY KEY,
name varchar2(50) NOT NULL,
value number,
gmt_create date NOT NULL DEFAULT sysdate,
gmt_modified date NOT NULL DEFAULT sysdate
);
Query OK, 0 rows affected
obclient [SYS]> INSERT INTO t_merge(id,name,value) VALUES(4,'JP',10004),(5,'CN',10005);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient [SYS]> SELECT * FROM t_merge;
+----+------+-------+------------+--------------+
| ID | NAME | VALUE | GMT_CREATE | GMT_MODIFIED |
+----+------+-------+------------+--------------+
| 4 | JP | 10004 | 31-OCT-22 | 31-OCT-22 |
| 5 | CN | 10005 | 31-OCT-22 | 31-OCT-22 |
+----+------+-------+------------+--------------+
2 rows in set
Example 1: Use the MERGE INTO statement to insert the data in the t_merge table into the t_insert table.
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 31-OCT-22 |
| 2 | US | 10002 | 31-OCT-22 |
| 3 | EN | 10003 | 31-OCT-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> MERGE INTO t_insert a
USING (SELECT id,name,value,gmt_create FROM t_merge) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.name = b.name, a.value = b.value, a.gmt_create = sysdate
WHEN NOT MATCHED THEN
INSERT (a.id,a.name,a.value) VALUES(b.id, b.name, b.value);
Query OK, 3 rows affected
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 31-OCT-22 |
| 2 | US | 10002 | 31-OCT-22 |
| 3 | EN | 10003 | 31-OCT-22 |
| 4 | JP | 10004 | 31-OCT-22 |
| 5 | CN | 10005 | 31-OCT-22 |
+----+------+-------+------------+
5 rows in set
In this example, the id column in the destination t_insert table is the primary key column and must meet the UNIQUE constraint. The records (4,'JP',10004) and (5,'CN',10005) in the source table do not exist in the destination table, and therefore are inserted into the destination table.
Records in the source table exist in the destination table
If the records in the source table exist in the destination table, you can use the MERGE INTO statement to update these records in the destination table.
The information about the source t_merge table is as follows:
obclient [SYS]> CREATE TABLE t_merge(
id number NOT NULL PRIMARY KEY,
name varchar2(50) NOT NULL,
value number,
gmt_create date NOT NULL DEFAULT sysdate,
gmt_modified date NOT NULL DEFAULT sysdate
);
Query OK, 0 rows affected
obclient [SYS]> INSERT INTO t_merge(id,name,value) VALUES(3,'CN',10003),(4,'JP',10004),(5,'CN',10005);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient [SYS]> SELECT * FROM t_merge;
+----+------+-------+------------+--------------+
| ID | NAME | VALUE | GMT_CREATE | GMT_MODIFIED |
+----+------+-------+------------+--------------+
| 3 | CN | 10003 | 01-NOV-22 | 01-NOV-22 |
| 4 | JP | 10004 | 01-NOV-22 | 01-NOV-22 |
| 5 | CN | 10005 | 01-NOV-22 | 01-NOV-22 |
+----+------+-------+------------+--------------+
3 rows in set
Example 2: Use the MERGE INTO statement to update the data in the name and value columns of the t_insert table.
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 31-OCT-22 |
| 2 | US | 10002 | 31-OCT-22 |
| 3 | EN | 10003 | 31-OCT-22 |
+----+------+-------+------------+
3 rows in set
obclient [SYS]> MERGE INTO t_insert a
USING (SELECT id,name,value,gmt_create FROM t_merge ) b
ON (a.id = b.id)
WHEN MATCHED THEN
UPDATE SET a.name = b.name, a.value = b.value, a.gmt_create = sysdate
WHEN NOT MATCHED THEN
INSERT (a.id,a.name,a.value) VALUES(b.id, b.name, b.value);
Query OK, 3 rows affected
obclient [SYS]> SELECT * FROM t_insert;
+----+------+-------+------------+
| ID | NAME | VALUE | GMT_CREATE |
+----+------+-------+------------+
| 1 | CN | 10001 | 31-OCT-22 |
| 2 | US | 10002 | 31-OCT-22 |
| 3 | CN | 10003 | 01-NOV-22 |
| 4 | JP | 10004 | 01-NOV-22 |
| 5 | CN | 10005 | 01-NOV-22 |
+----+------+-------+------------+
5 rows in set
In this example, the id column in the destination table t_insert is the primary key column and must meet the UNIQUE constraint. The (3,'CN',10003) record in the source t_merge table already exists in the destination table t_insert, and therefore is updated in the destination table.
Use a DBLink to replace data
The current OceanBase Database version allows you to use a DBLink to write data into OceanBase Database's Oracle mode and Oracle Database.
The following example shows how to replace data of the t3 table in the remote database connected through a DBLink:
obclient> SELECT * FROM t3@orcl_dblink;
+------+------+
| C1 | C2 |
+------+------+
| 3 | 3 |
| 4 | 4 |
+------+------+
2 row in set
obclient> SELECT * FROM t4;
+------+------+
| C1 | C2 |
+------+------+
| 5 | 5 |
| 6 | 6 |
+------+------+
2 row in set
obclient> MERGE INTO t3@orcl_dblink a
USING (SELECT C1,C2 FROM t4 ) b
ON (a.C1 = b.C1)
WHEN MATCHED THEN
UPDATE SET a.C2 = b.C2
WHEN NOT MATCHED THEN
INSERT (a.C1,a.C2) VALUES(b.C1, b.C2);
Query OK, 2 rows affected
obclient> commit;
Query OK, 0 rows affected
obclient> SELECT * FROM t3@orcl_dblink;
+------+------+
| C1 | C2 |
+------+------+
| 3 | 3 |
| 4 | 4 |
| 5 | 5 |
| 6 | 6 |
+------+------+
4 row in set
In the Oracle mode of OceanBase Database, you can use the INSERT, DELETE, UPDATE, and MERGE INTO statements to write data from local tables to remote tables. For more information, see Use a DBLink to modify data in a remote database.