Updating and Inserting Data by Using the MERGE INTO Statement

To add all or a large amount of data in a table to an existing table, you can run the MERGE INTO statement in openGauss to merge the two tables so that data can be quickly added to the existing table.

The MERGE INTO statement matches data in a source table with that in a target table based on a join condition. If data matches, UPDATE will be executed on the target table. Otherwise, INSERT will be executed. This statement is a convenient way to combine multiple operations and avoids multiple INSERT or UPDATE statements.

Prerequisites

You have the INSERT and UPDATE permissions for the target table and the SELECT permission for the source table.

Procedure

1. Create a source table named products and insert data.

   openGauss=# CREATE TABLE products 
   ( product_id INTEGER, 
     product_name VARCHAR2(60), 
     category VARCHAR2(60) 
   );
   
   openGauss=# INSERT INTO products VALUES 
   (1502, 'olympus camera', 'electrncs'),
   (1601, 'lamaze', 'toys'),
   (1666, 'harry potter', 'toys'),
   (1700, 'wait interface', 'books'); 
   

2. Create a target table named newproducts and insert data.

   openGauss=# CREATE TABLE newproducts 
   ( product_id INTEGER, 
     product_name VARCHAR2(60), 
     category VARCHAR2(60) 
   ); 
   
   openGauss=# INSERT INTO newproducts VALUES 
   (1501, 'vivitar 35mm', 'electrncs'),
   (1502, 'olympus ', 'electrncs'),
   (1600, 'play gym', 'toys'),
   (1601, 'lamaze', 'toys'), 
   (1666, 'harry potter', 'dvd'); 
   

3. Run the MERGE INTO statement to merge data in the source table products into the target table newproducts.

   openGauss=# MERGE INTO newproducts np    
   USING products p    
   ON (np.product_id = p.product_id )    
   WHEN MATCHED THEN     
     UPDATE SET np.product_name = p.product_name, np.category = p.category 
   WHEN NOT MATCHED THEN     
     INSERT VALUES (p.product_id, p.product_name, p.category) ; 
   

   For details on parameters in the statement, see Table 1. For more information, see MERGE INTO.

   Table 1 Parameters in the MERGE INTO statement

   Parameter	Description	Example Value
   INTO clause	Specifies a target table that is to be updated or has data to be inserted. A table alias is supported.	Value: newproducts np The table name is newproducts and the alias is np.
   USING clause	Specifies a source table. A table alias is supported.	Value: products p The table name is products and the alias is p.
   ON clause	Specifies a join condition between a target table and a source table. Columns in the join condition cannot be updated.	Value: np.product_id = p.product_id The join condition is that the product_id column in the target table newproducts has equivalent values as the product_id column in the source table products.
   WHEN MATCHED clause	Performs UPDATE if data in the source table matches that in the target table based on the condition. Only one WHEN MATCHED clause can be specified. The WHEN MATCHED clause can be omitted. If it is omitted, no operation will be performed on the rows that meet the condition in the ON clause.	Value: WHEN MATCHED THEN UPDATE SET np.product_name = p.product_name, np.category = p.category When the condition in the ON clause is met, the values of the product_name and category columns in the target table newproducts are replaced with the values in the corresponding columns in the source table products.
   WHEN NOT MATCHED clause	Performs INSERT if data in the source table does not match that in the target table based on the condition. Only one WHEN NOT MATCHED clause can be specified. The WHEN NOT MATCHED clause can be omitted. An INSERT clause can contain only one VALUES. The WHEN MATCHED and WHEN NOT MATCHED clauses can be exchanged in sequence. One of them can be omitted, but they cannot be omitted at the same time.	Value: WHEN NOT MATCHED THEN INSERT VALUES (p.product_id, p.product_name, p.category) Insert rows in the source table products that do not meet the condition in the ON clause into the target table newproducts.

4. Query the target table newproducts after the merge.

   openGauss=# SELECT * FROM newproducts;
   

   The command output is as follows:

    product_id |  product_name  | category
   ------------+----------------+-----------
          1501 | vivitar 35mm   | electrncs
          1502 | olympus camera | electrncs
          1666 | harry potter   | toys
          1600 | play gym       | toys
          1601 | lamaze         | toys
          1700 | wait interface | books
   (6 rows)