MERGE INTO

功能描述

通过MERGE INTO语句,将目标表和源表中数据针对关联条件进行匹配,若关联条件匹配时对目标表进行UPDATE,无法匹配时对目标表执行INSERT。此语法可以很方便地用来合并执行UPDATE和INSERT,避免多次执行。

注意事项

  • 进行MERGE INTO操作的用户需要同时拥有目标表的UPDATE和INSERT权限,以及源表的SELECT权限。
  • 不支持重分布过程中MERGE INTO。

语法格式

  1. MERGE INTO table_name [ [ AS ] alias ]
  2. USING { { table_name | view_name } | subquery } [ [ AS ] alias ]
  3. ON ( condition )
  4. [
  5. WHEN MATCHED THEN
  6. UPDATE SET { column_name = { expression | DEFAULT } |
  7. ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) } [, ...]
  8. [ WHERE condition ]
  9. ]
  10. [
  11. WHEN NOT MATCHED THEN
  12. INSERT { DEFAULT VALUES |
  13. [ ( column_name [, ...] ) ] VALUES ( { expression | DEFAULT } [, ...] ) [, ...] [ WHERE condition ] }
  14. ];

参数说明

  • INTO子句

    指定正在更新或插入的目标表。目标表为复制表时,暂不支持目标表中某列默认值为volatile函数(如自增列),enable_stream_operator=off时目标表需要包含主键或带有unique not null。

    • talbe_name

      目标表的表名。

    • alias

      目标表的别名。

      取值范围:字符串,符合标识符命名规范。

  • USING子句

    指定源表,源表可以为表、视图或子查询。目标表为复制表时,暂不支持USING子句中包含非复制表。

  • ON子句

    关联条件,用于指定目标表和源表的关联条件。不支持更新关联条件中的字段。

  • WHEN MATCHED子句

    当源表和目标表中数据针对关联条件可以匹配上时,选择WHEN MATCHED子句进行UPDATE操作。

    不支持更新分布列。不支持更新系统表、系统列。

  • WHEN NOT MATCHED子句

    当源表和目标表中数据针对关联条件无法匹配时,选择WHEN NOT MATCHED子句进行INSERT操作。

    不支持INSERT子句中包含多个VALUES。

    WHEN MATCHED和WHEN NOT MATCHED子句顺序可以交换,可以缺省其中一个,但不能同时缺省,不支持同时指定两个WHEN MATCHED或WHEN NOT MATCHED子句。

  • DEFAULT

    用对应字段的缺省值填充该字段。

    如果没有缺省值,则为NULL。

  • WHERE condition

    UPDATE子句和INSERT子句的条件,只有在条件满足时才进行更新操作,可缺省。不支持WHERE条件中引用系统列。

示例

  1. -- 创建目标表products和源表newproducts,并插入数据
  2. postgres=# CREATE TABLE products
  3. (
  4. product_id INTEGER,
  5. product_name VARCHAR2(60),
  6. category VARCHAR2(60)
  7. );
  8. postgres=# INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');
  9. postgres=# INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');
  10. postgres=# INSERT INTO products VALUES (1600, 'play gym', 'toys');
  11. postgres=# INSERT INTO products VALUES (1601, 'lamaze', 'toys');
  12. postgres=# INSERT INTO products VALUES (1666, 'harry potter', 'dvd');
  13. postgres=# CREATE TABLE newproducts
  14. (
  15. product_id INTEGER,
  16. product_name VARCHAR2(60),
  17. category VARCHAR2(60)
  18. );
  19. postgres=# INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');
  20. postgres=# INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');
  21. postgres=# INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');
  22. postgres=# INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');
  23. -- 进行MERGE INTO操作
  24. postgres=# MERGE INTO products p
  25. USING newproducts np
  26. ON (p.product_id = np.product_id)
  27. WHEN MATCHED THEN
  28. UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'
  29. WHEN NOT MATCHED THEN
  30. INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';
  31. MERGE 4
  32. -- 查询更新后的结果
  33. postgres=# SELECT * FROM products ORDER BY product_id;
  34. product_id | product_name | category
  35. ------------+----------------+-----------
  36. 1501 | vivitar 35mm | electrncs
  37. 1502 | olympus camera | electrncs
  38. 1600 | play gym | toys
  39. 1601 | lamaze | toys
  40. 1666 | harry potter | toys
  41. 1700 | wait interface | books
  42. (6 rows)
  43. -- 删除表
  44. postgres=# DROP TABLE products;
  45. postgres=# DROP TABLE newproducts;