MERGE

Purpose

This statement is used to update data in a target table based on data from a source table. It allows for inserting, updating, or deleting data in the target table.

Limitations and considerations

• If a partition of the target table is specified, the update operation will only affect data in that specific partition of the target table.

• If partitions are specified for both the target and source tables, the matching operation will be based on data in the specified partitions of both tables.

• If there is no matching data in the target and source tables, the update operation will not be performed.

Syntax

MERGE [hint_options] INTO target_table_name [PARTITION(partition_name_list)] [opt_alias]
    USING source_table_name [PARTITION(partition_name_list)] [opt_alias]
        ON (expr)
    [merge_update_clause]
    [merge_insert_clause];

partition_name_list:
    partition_name [, partition_name ...]

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

Examples

The tables and data in the examples are based on the following definitions:

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 data in tbl1 based on data in tbl2:

• If a row with the same value of tbl1.col1 exists in tbl2, and if tbl1.col2 is null, use tbl2.col2 to update tbl1.col2. If the updated value of tbl1.col2 is greater than or equal to 10, delete the row.

• If no row with the same value of tbl2.col1 exists in tbl1, insert the row from tbl2 into tbl1, but only if tbl2.col2 is less 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

• Update data in a specified partition.

  1. Create the test partitioned tables test_tbl3 and test_tbl4.

     CREATE TABLE test_tbl3 (col1 INT, col2 INT, PRIMARY KEY(col1)) 
         PARTITION BY RANGE(col1)
         (PARTITION p0 VALUES LESS THAN(10),
          PARTITION p1 VALUES LESS THAN(20),
          PARTITION p2 VALUES LESS THAN(30),
          PARTITION p3 VALUES LESS THAN(40)
         );
     

     CREATE TABLE test_tbl4 (col1 INT, col2 INT, PRIMARY KEY(col1)) 
         PARTITION BY RANGE(col1)
         (PARTITION p0 VALUES LESS THAN(5),
          PARTITION p1 VALUES LESS THAN(15),
          PARTITION p2 VALUES LESS THAN(25),
          PARTITION p3 VALUES LESS THAN(35)
         );
     

  2. Insert test data.

     INSERT INTO test_tbl3 VALUES(1, 1),(5, 5),(11, 11),(13, 13),(20, 20);
     

     INSERT INTO test_tbl4 VALUES(1, 11),(5, 55),(11, 1111),(13, 1313),(20, 2020);
     

  3. View the data in partition p0 of test_tbl3.

     SELECT * FROM test_tbl3 PARTITION(p0);
     

     The returned result is as follows:

     +------+------+
     | COL1 | COL2 |
     +------+------+
     |    1 |    1 |
     |    5 |    5 |
     +------+------+
     2 rows in set
     

  4. View the data in partitions p0 and p1 of test_tbl4.

     SELECT * FROM test_tbl4 PARTITION(p0, p1);
     

     The returned result is as follows:

     +------+------+
     | COL1 | COL2 |
     +------+------+
     |    1 |   11 |
     |    5 |   55 |
     |   11 | 1111 |
     |   13 | 1313 |
     +------+------+
     4 rows in set
     

  5. Match data in partitions p0 and p1 of test_tbl4 with data in partition p0 of test_tbl3. If the values in the col1 column are equal in both tables, update the value in the col2 column of test_tbl3 to 10000.

     MERGE INTO test_tbl3 PARTITION(p0) t3
         USING test_tbl4 PARTITION(p0, p1) t4
             ON (t3.col1 = t4.col1)
         WHEN MATCHED THEN UPDATE SET t3.col2 = 10000;
     

  6. View the data in test_tbl3.

     SELECT * FROM test_tbl3;
     

     The returned result is as follows:

     +------+-------+
     | COL1 | COL2  |
     +------+-------+
     |    1 | 10000 |
     |    5 | 10000 |
     |   11 |    11 |
     |   13 |    13 |
     |   20 |    20 |
     +------+-------+
     5 rows in set