Description
You can execute the MERGE statement to update data for a destination table based on a source table. For example, you can insert, update, or delete rows.
Syntax
MERGE [hint_options]
INTO table_factor [opt_alias]
USING table_factor [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 | The hint. |
| table_factor | The names of source and destination tables. |
| ON expr | The JOIN conditions of source and destination tables. |
| update_asgn_list | Assign values for the update. |
| WHERE expr | The conditions required to trigger the update, delete, or insert operation. |
Examples
The following examples are based on tables t1 and t2.
create table t1 (c1 int, c2 int);
create table t2 (c1 int, c2 int);
insert into t1 values (0, 0);
insert into t1 values (1, null);
insert into t1 values (2, null);
insert into t2 values (1, 1);
insert into t2 values (2, 20);
insert into t2 values (3, 3);
insert into t2 values (4, 40);
Update table t1 based on table t2.
- Assume that a value in column c1 of table t1 is equal to a value in column c1 of table t2.
- Assume that values in column c1 of table t2 do not match those of table t1.
merge into t1 using t2 on (t1.c1 = t2.c1)
when matched then update set c2 = t2.c2 where t1.c2 is null delete where t1.c2 >= 10
when not matched then insert values (t2.c1, t2.c2) where t2.c2 < 10;
Query OK, 3 rows affected (0.02 sec)
select * from t1;
+------+------+
| C1 | C2 |
+------+------+
| 0 | 0 |
| 1 | 1 |
| 3 | 3 |
+------+------+