Purpose
You can use this statement to insert, update, or delete rows in a target table based on the source table.
Syntax
MERGE [hint_options] INTO target_table_name [opt_alias]
USING source_table_name [opt_alias]
ON (expr)
[merge_update_clause] [merge_insert_clause]
merge_update_clause:
WHEN MATCHED THEN UPDATE SET update_asgn_list [WHERE expr] [DELETE WHERE expr]
merge_insert_clause:
WHEN NOT MATCHED THEN INSERT opt_insert_columns VALUES '(' insert_vals ')' [WHERE expr]
Parameters
Parameter |
Description |
|---|---|
| hint_options | Optional. The hint options. |
| target_table_name | The name of the target table where rows are to be updated or inserted. |
| source_table_name | The name of the source table. |
| ON (expr) | The condition for joining the source and target tables. |
| update_asgn_list | Updates the value assignment operation for the statement. |
| WHERE expr | The conditions for triggering an update, delete, or insert operation. |
Examples
Sample tables and their data are defined as follows:
obclient> CREATE TABLE tbl1 (col1 INT, col2 INT);
Query OK, 0 rows affected
obclient> CREATE TABLE tbl2 (col1 INT, col2 INT);
Query OK, 0 rows affected
obclient> INSERT INTO tbl1 VALUES(0, 0),(1, null),(2, null);
Query OK, 3 row affected
obclient> INSERT INTO tbl2 VALUES(1, 1),(2, 20),(3, 3),(4, 40);
Query OK, 4 row affected
obclient> SELECT * FROM tbl1;
+------+------+
| COL1 | COL2 |
+------+------+
| 0 | 0 |
| 1 | NULL |
| 2 | NULL |
+------+------+
3 rows in set
obclient> SELECT * FROM tbl2;
+------+------+
| COL1 | COL2 |
+------+------+
| 1 | 1 |
| 2 | 20 |
| 3 | 3 |
| 4 | 40 |
+------+------+
4 rows in set
Update the data of tbl1 based on the data of tbl2.
For each row where the condition
tbl1.col1 = tbl2.col1is true in tbl1, OceanBase Database updates the row with the corresponding value oftbl2.col2iftbl1.col2is empty in the row. If the updatedtbl1.col2is greater than or equal to 10, the row is deleted.If the condition
tbl1.col1 = tbl2.col1is not true for any rows in tbl1, OceanBase Database inserts the unmatched rows of tbl2 into tbl1, with the prerequisite thattbl2.col2in the unmatched rows is smaller than 10.
obclient>MERGE INTO tbl1 USING tbl2 ON (tbl1.col1 = tbl2.col1)
WHEN MATCHED THEN UPDATE SET tbl1.col2 = tbl2.col2 WHERE tbl1.col2 IS NULL DELETE
WHERE tbl1.col2 >= 10
WHEN NOT MATCHED THEN INSERT VALUES(tbl2.col1, tbl2.col2)
WHERE tbl2.col2 < 10;
Query OK, 3 rows affected
obclient> SELECT * FROM tbl1;
+------+------+
| COL1 | COL2 |
+------+------+
| 0 | 0 |
| 1 | 1 |
| 3 | 3 |
+------+------+
3 rows in set