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 of the target table is specified, the update operation will only affect the data in that partition.

• If partitions of both the target and source tables are specified, 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

The 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 following 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 the data in the tbl1 table:

   SELECT * FROM tbl1;
   

   Execution result:

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

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

• If a row with the same value of tbl1.col1 as 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 no row with the same value of tbl2.col1 exists in tbl1, insert the row from tbl2 into tbl1, but only insert rows where 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 the data.

  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 the p0 partition 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 the p0 and p1 partitions 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 the data in the p0 and p1 partitions of test_tbl4 with the data in the p0 partition of test_tbl3. If the values in the col1 column of both tables are equal, 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