云数据库 GaussDB-MERGE INTO:示例

时间：2025-02-12 15:05:44

云数据库 GaussDB

示例

-- 创建目标表products和源表newproducts，并插入数据gaussdb=# CREATE TABLE products(product_id INTEGER,product_name VARCHAR2(60),category VARCHAR2(60));gaussdb=# INSERT INTO products VALUES (1501, 'vivitar 35mm', 'electrncs');gaussdb=# INSERT INTO products VALUES (1502, 'olympus is50', 'electrncs');gaussdb=# INSERT INTO products VALUES (1600, 'play gym', 'toys');gaussdb=# INSERT INTO products VALUES (1601, 'lamaze', 'toys');gaussdb=# INSERT INTO products VALUES (1666, 'harry potter', 'dvd');gaussdb=# CREATE TABLE newproducts(product_id INTEGER,product_name VARCHAR2(60),category VARCHAR2(60));gaussdb=# INSERT INTO newproducts VALUES (1502, 'olympus camera', 'electrncs');gaussdb=# INSERT INTO newproducts VALUES (1601, 'lamaze', 'toys');gaussdb=# INSERT INTO newproducts VALUES (1666, 'harry potter', 'toys');gaussdb=# INSERT INTO newproducts VALUES (1700, 'wait interface', 'books');-- 进行MERGE INTO操作gaussdb=# 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-- 查询更新后的结果gaussdb=# 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)-- 删除表gaussdb=# DROP TABLE products;gaussdb=# DROP TABLE newproducts;