All Products
Search
Document Center

MERGE

Last Updated: Jun 18, 2021

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.

  1. Assume that a value in column c1 of table t1 is equal to a value in column c1 of table t2.

    1. If a value in column c2 of table t1 is NULL, the system updates the value by using the value in column c2 of table t2.

    2. After the update, if the value in column c2 of table t1 is greater than or equal to 0, the system deletes the value.

  1. Assume that values in column c1 of table t2 do not match those of table t1.

    1. Find the values in column c2 of table t2 that are less than 10, and insert them into 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 |
+------+------+