In a batch task, you may need to perform many insert or update operations on the target table. To insert a record if it does not exist and update a record if it exists, you can use the MERGE statement.
MERGE statement
The syntax for the MERGE statement 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 | Examples |
|---|---|---|---|
| MERGE INTO table_name alias1 | Yes | The name and alias of the table into which data is to be inserted. | MERGE INTO t_merge t2 |
| USING (table|view|sub_query) alias2 | Yes | The source table (view, subquery) and its alias. | USING (SELECT id,name,value,gmt_create FROM t_insert ) t1 |
| ON (join condition) | No | The judgment condition. | ON (t2.id=t1.id) |
| 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 MATCHED THEN UPDATE t_merge t2 SET t2.name=t1.name, t2.value=t1.value, t2.gmt_modified=sysdate |
| WHEN NOT MATCHED THEN INSERT (column_list) VALUES (column_values); | Yes | Specifies to execute the INSERT statement when the specified condition is not matched. | HEN NOT MATCHED THEN INSERT (t2.id,t2.name,t2.value) values(t1.id, t1.name, t1.value) |
Examples
Use the MERGE INTO statement to update the data in the name and value columns in the t_merge table.
obclient> 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> INSERT INTO t_merge(id,name,value) VALUES(1,'CN',1),(2,'US',2);
Query OK, 2 rows affected
Records: 2 Duplicates: 0 Warnings: 0
obclient> MERGE INTO t_merge t2
USING (SELECT id,name,value,gmt_create FROM t_insert ) t1
ON (t2.id=t1.id)
WHEN MATCHED THEN
UPDATE SET t2.name=t1.name, t2.value=t1.value, t2.gmt_modified=sysdate
WHEN NOT MATCHED THEN
INSERT (t2.id,t2.name,t2.value) VALUES(t1.id, t1.name, t1.value);
Query OK, 3 rows affected
obclient> SELECT * FROM t_merge;
+----+------+-------+------------+--------------+
| ID | NAME | VALUE | GMT_CREATE | GMT_MODIFIED |
+----+------+-------+------------+--------------+
| 1 | CN | 10002 | 01-JAN-70 | 01-JAN-70 |
| 2 | US | 10003 | 01-JAN-70 | 01-JAN-70 |
| 3 | EN | 10004 | 01-JAN-70 | 01-JAN-70 |
+----+------+-------+------------+--------------+
3 rows in set