MERGE

Purpose

This statement is used to update the data in a target table based on the data in a source table. It can perform insert, update, or delete operations on the target table.

Limitations and considerations

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

• If partitions are specified for both the target and source tables, the matching operation will be based on the 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 [PARTITION(partition_name_list)] [t_alias]
USING { source_table | subquery | TABLE(collection_expression) } [PARTITION(partition_name_list)] [s_alias]
ON (condition)
[merge_update_clause] [merge_insert_clause]
| [merge_insert_clause] [merge_update_clause]

partition_name_list:
    partition_name [, partition_name ...]

merge_update_clause:
    WHEN MATCHED [AND condition] THEN UPDATE SET update_asgn_list [WHERE condition] [DELETE WHERE condition]

merge_insert_clause:
    WHEN NOT MATCHED [AND condition] THEN INSERT [(column [, column ...])] VALUES (expr [, expr ...]) [WHERE condition]

hint_options:
    /*+ hint_text */

t_alias:
    [AS] alias

s_alias:
    [AS] alias

Parameters

MERGE statement is used to update or insert data into the target table based on the data in the source table. You can specify hint_options to optimize the execution plan.

Considerations

Examples

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

1. Create a test table:

   CREATE TABLE tbl1 (col1 INT, col2 INT);
   CREATE TABLE tbl2 (col1 INT, col2 INT);
   

2. Insert test data:

   INSERT INTO tbl1 VALUES(0, 0),(1, null),(2, null);
   

   INSERT INTO tbl2 VALUES(1, 1),(2, 20),(3, 3),(4, 40);
   

3. Query the table data:

   Query data from the tbl1 table:

   SELECT * FROM tbl1;
   

   Execution result:

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    0 |    0 |
   |    1 | NULL |
   |    2 | NULL |
   +------+------+
   3 rows in set
   

   Query data from the tbl2 table:

   SELECT * FROM tbl2;
   

   Execution result:

   +------+------+
   | COL1 | COL2 |
   +------+------+
   |    1 |    1 |
   |    2 |   20 |
   |    3 |    3 |
   |    4 |   40 |
   +------+------+
   4 rows in set
   

Update the data in tbl1 based on the data in tbl2:

• If a row in tbl1 with the same value in tbl1.col1 exists in tbl2, and if tbl1.col2 is empty, 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 a row in tbl2 with the same value in tbl2.col1 does not exist 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

• Specify a partition to update data.

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

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