Help Center/ GaussDB/ Developer Guide(Distributed_2.x)/ Importing Data/ Updating Data in a Table/ Updating and Inserting Data by Using the MERGE INTO Statement
Updated on 2024-10-14 GMT+08:00
View PDF
Share

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 GaussDB 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. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
    		 
    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. 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
    		 
    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. 

    1
2
3
4
5
6
7
    		 
    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.
    • The target table can be copied, but the copied table cannot contain columns (such as auto-increment columns) that contain volatile functions. If enable_stream_operator is set to off, the target replication table must contain a primary key or a column must meet the unique not null constraint.

    Value: newproducts np

    The table name is newproducts and the alias is np.

    USING clause

    Specifies a source table. A table alias is supported.

    If the target table is a replication table, the source table must also be a replication table.

    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.
    • Columns involved in the distribution key of the target table cannot be updated.

    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 products.

  4. Query the target table newproducts after the merge. 

    1
    		 
    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)

Parent topic: Updating Data in a Table