MERGE

描述

该语句用来根据一张源表更新一张目标表的数据，可以对目标表进行插入、更新或者删除。

使用限制及注意事项

• 如果指定了目标表的分区，那么更新操作只会影响目标表中具有指定分区的数据。

• 如果指定了目标表和源表的分区，那么匹配操作将根据目标表中指定分区和源表中指定分区的数据进行匹配。

• 如果目标表和源表中没有匹配的数据，那么不会执行更新操作。

语法

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]

参数解释

示例

示例中的表和数据基于以下定义：

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

根据 tbl2 的数据更新 tbl1 的数据：

• 如果 tbl1.col1 在 tbl2 中能够找到 tbl1.col1 取值相同的行，如果 tbl1.col2 取值为空，那么使用 tbl2.col2 更新 tbl1.col2；如果更新之后 tbl1.col2 >= 10，那么删除该行。

• 如果 tbl2.col1 在 tbl1 中找不到取值相同的行，将 tbl2 中的这行插入到 tbl1 中，且只插入满足 tbl2.col2 < 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

• 指定分区更新数据。

  1. 创建测试分区表 test_tbl3 和 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 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. 查看 test_tbl3 中分区 p0 的数据。

     SELECT * FROM test_tbl3 PARTITION(p0);
     

     返回结果如下：

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

  4. 查看 test_tbl4 中分区 p0 和 p1 的数据。

     SELECT * FROM test_tbl4 PARTITION(p0, p1);
     

     返回结果如下：

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

  5. 将 test_tbl4 中分区 p0 和 p1 的数据与 test_tbl3 中分区 p0 的数据进行匹配，如果两个表中的数据在 col1 列上相等，则将 test_tbl3 中的 col2 列的值更新为 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. 查看 test_tbl3 中的数据。

     SELECT * FROM test_tbl3;
     

     返回结果如下：

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