On this page

    Merging

    The MERGE command runs insert, update, or delete 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 <column1_name> = <value1> [, <column2_name> = <value2> ... ]
    WHEN NOT MATCHED THEN
      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

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

    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 [ (<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.