On this page

    Merging

    The MERGE command runs insert or update operations on a target table from the results of a join with a source table.

    Syntax
    MERGE INTO <table_path>.<table_name> [AS target_alias]
    USING {
      (select_statement) | 
      <table_path>.<table_name> [AS source_alias]
    }
    ON ( condition )
    [ WHEN MATCHED THEN 
      UPDATE SET * | 
      UPDATE SET <column1_name> = <value1> [, <column2_name> = <value2> ... ] ]
    [ WHEN NOT MATCHED THEN
      INSERT * |
      INSERT [ (<column_name> [, ...]) ]
      VALUES (expression [, ...]) ]
    

    Parameters

    <table_path>

    String

    The path in which the table is located.


    <table_name>

    String

    The name of the table with data that you want to merge.


    select_statement

    String

    Optional

    The query that produces data that you want to merge.


    ON ( condition )

    String

    Specifies the conditions on which source table joins with target table to determine whether and where they match.


    WHEN MATCHED THEN

    String

    Optional

    Specifies that all rows of target table which match the rows returned from the source table due to the search condition are either updated according to the UPDATE SET clause.


    UPDATE SET *

    String

    Equivalent to UPDATE SET col1 = source.col1 [, col2 = source.col2 ...] for all columns of the target table. This operation assumes that the source table has the same columns as those in the target table. If this assumption is incorrect, the MERGE command fails and returns an error message. Columns must either be of the same data type, or implicit conversion between source and target data types must be supported.


    UPDATE SET <column1_name> = <value1> [, <column2_name> = <value2> ... ]

    String

    Sets the value of one or more columns. The value can be any expression or sub-query that returns a single value.


    WHEN NOT MATCHED THEN

    String

    Optional

    Specifies that a row is inserted into target table for every row, returned from the source table due to the search condition, that does not match a row in the target table. The values to insert are specified by the INSERT clause.


    INSERT SET *

    String

    Equivalent to INSERT (col1 [, col2 ...]) VALUES (source.col1 [, source.col2 ...]) for all columns of the target table. This operation assumes that the source table has the same columns as those in the target table. If this assumption is incorrect, the MERGE command fails and returns an error message. Columns must either be of the same data type, or implicit conversion between source and target data types must be supported.


    INSERT [ (<column_name> [, ...]) ] VALUES (expression [, ...])

    String

    Specifies the values to insert into a new row of the target table for every row, returned from the source table due to the search condition, that does not match a row in the target table.