MERGE

Purpose

You can use this statement to insert, update, or delete rows in a target table based on the source table.

Limitations and considerations

• If the partitions of the target table are specified, the update operation will only affect the data in the specified partitions of the target table.

• If the partitions of both the target table and the source table are specified, the matching operation will match the data in the specified partitions of the target table with the data in the specified partitions of the source table.

• If no matching data is found between the target table and the source table, the update operation will not be executed.

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

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.col1 is true in tbl1, OceanBase Database updates the row with the corresponding value of tbl2.col2 if tbl1.col2 is empty in the row. If the updated tbl1.col2 is greater than or equal to 10, the row is deleted.

• If the condition tbl1.col1 = tbl2.col1 is not true for any rows in tbl1, OceanBase Database inserts the unmatched rows of tbl2 into tbl1, with the prerequisite that tbl2.col2 in 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

• Specify partitions to update data.

  1. Create the test partition 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 query 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 query result is as follows:

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

  5. Match the data in partitions p0 and p1 of test_tbl4 with the data in partition p0 of test_tbl3. If the values in the col1 column are equal in both tables, update 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 query result is as follows:

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