云数据库 GaussDB-MERGE INTO:示例

时间：2023-11-01 16:22:04

云数据库 GaussDB

示例

-- 创建目标表products和源表newproducts，并插入数据openGauss=# CREATE TABLE products(product_id INTEGER,product_name VARCHAR2(60),category VARCHAR2(60));openGauss=# INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');openGauss=# INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');openGauss=# INSERT INTO products VALUES (1600, 'play gym', 'toys');openGauss=# INSERT INTO products VALUES (1601, 'lamaze', 'toys');openGauss=# INSERT INTO products VALUES (1666, 'harry potter', 'dvd');openGauss=# CREATE TABLE newproducts(product_id INTEGER,product_name VARCHAR2(60),category VARCHAR2(60));openGauss=# INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');openGauss=# INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');openGauss=# INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');openGauss=# INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');-- 进行MERGE INTO操作openGauss=# MERGE INTO products p   USING newproducts np   ON (p.product_id = np.product_id)   WHEN MATCHED THEN    UPDATE SET p.product_name = np.product_name, p.category = np.category WHERE p.product_name != 'play gym'  WHEN NOT MATCHED THEN    INSERT VALUES (np.product_id, np.product_name, np.category) WHERE np.category = 'books';MERGE 4-- 查询更新后的结果openGauss=# SELECT * FROM products ORDER BY product_id; product_id |  product_name  | category  ------------+----------------+-----------       1501 | vivitar 35mm   | electrncs       1502 | olympus camera | electrncs       1600 | play gym       | toys       1601 | lamaze         | toys       1666 | harry potter   | toys       1700 | wait interface | books(6 rows)-- 删除表openGauss=# DROP TABLE products;openGauss=# DROP TABLE newproducts;