MERGE

insert, update rows of a table based upon source data

Synopsis

MERGE INTO target_table_name [ [ AS ] target_alias ]
USING data_source
ON join_condition
when_clause [...]

where data_source is

{ source_table_name |
( source_query )
}
[ [ AS ] source_alias ]

and when_clause is

{ WHEN MATCHED THEN { merge_update } |
WHEN NOT MATCHED THEN { merge_insert }
}

and merge_insert is

INSERT [( column_name [, ...] )]
{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES
}
[ WHERE condition ]

and merge_update is

UPDATE SET { column_name = { 
    expression | DEFAULT } |
( column_name [, ...] ) = ( { 
    expression | DEFAULT } [, ...] )
} [, ...]
[ WHERE condition ]

Description

MERGE performs actions that modify rows in the target_table_name, using the data_source. MERGE provides a single SQL statement that can conditionally INSERT or UPDATE rows, a task that would otherwise require multiple procedural language statements.

First, the MERGE command performs a join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row the status of MATCHED or NOT MATCHED is set just once, after which WHEN clauses are evaluated in the order specified. If one of them is activated, the specified action occurs. No more than one WHEN clause can be activated for any candidate change row.

MERGE actions have the same effect as regular UPDATE, INSERT commands of the same names. The syntax of those commands is different, notably that there is no tablename is specified. All actions refer to the target_table_name.

There is no MERGE privilege. You must have the UPDATE privilege on the column(s) of the target_table_name referred to in the SET clause if you specify an update action, the INSERT privilege on the target_table_name if you specify an insert action. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are activated during the subsequent execution. You will require the SELECT privilege on the data_source and any column(s) of the target_table_name referred to in a condition.

MERGE is not supported if the target_table_name has RULES defined on it.

Caution

It is necessary to ensure that a record in the target table does not match multiple times with a record in the source table, as this may result in updated records in the target table not having a certain value.

Parameters

target_table_name

The name (optionally schema-qualified) of the target table to merge into.

target_alias

A substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, given MERGE foo AS f, the remainder of the MERGE statement must refer to this table as f not foo.

source_table_name

The name (optionally schema-qualified) of the source table, view or transition table.

source_query

A query (SELECT statement or VALUES statement) that supplies the rows to be merged into the target_table_name. Refer to the SELECT statement or VALUES statement for a description of the syntax.

source_alias

A substitute name for the data source. When an alias is provided, it completely hides whether table or query was specified.

join_condition

join_condition is an expression resulting in a value of type boolean (similar to a WHERE clause) that specifies which rows in the data_source match rows in the target_table_name.

Warning

Only columns from target_table_name that attempt to match data_source rows should appear in join_condition. join_condition subexpressions that only reference target_table_name columns can only affect which action is taken, often in surprising ways.

when_clause

At least one WHEN clause is required.

If the WHEN clause specifies WHEN MATCHED and the candidate change row matches a row in the target_table_name the WHEN clause is activated if the condition is absent or is present and evaluates to true. If the WHEN clause specifies WHEN NOT MATCHED and the candidate change row does not match a row in the target_table_name the WHEN clause is activated if the condition is absent or is present and evaluates to true.

merge_insert

The specification of an INSERT action that inserts one row into the target table. The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.

Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.

If the expression for any column is not of the correct data type, automatic type conversion will be attempted.

If target_table_name is a partitioned table, each row is routed to the appropriate partition and inserted into it. If target_table_name is a partition, an error will occur if one of the input rows violates the partition constraint.

Column names may not be specified more than once. INSERT actions cannot contain sub-selects.

Only one VALUES clause can be specified. The VALUES clause can only refer to columns from the source relation, since by definition there is no matching target row.

merge_update

The specification of an UPDATE action that updates the current row of the target_table_name. Column names may not be specified more than once.

Do not include the table name, as you would normally do with an UPDATE command. For example, UPDATE tab SET col = 1 is invalid.

column_name

The name of a column in the target_table_name. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) When referencing a column, do not include the table’s name in the specification of a target column.

expression

An expression to assign to the column. The expression can use the old values of this and other columns in the table.

condition

An expression that returns a value of type boolean. If this expression returns true then the WHEN clause will be activated and the corresponding action will occur for that row. The expression may not contain functions that possibly performs writes to the database.

A condition on a WHEN MATCHED clause can refer to columns in both the source and the target relation. A condition on a WHEN NOT MATCHED clause can only refer to columns from the source relation, since by definition there is no matching target row.

Examples

Perform maintenance on CustomerAccounts based upon new Transactions.

MERGE INTO CustomerAccount CA
USING RecentTransactions T
ON T.CustomerId = CA.CustomerId
    WHEN MATCHED THEN
    UPDATE SET Balance = Balance + TransactionValue
WHEN NOT MATCHED THEN
    INSERT (CustomerId, Balance)
    VALUES (T.CustomerId, T.TransactionValue);

notice that this would be exactly equivalent to the following statement because the MATCHED result does not change during execution

MERGE INTO CustomerAccount CA
USING (Select CustomerId, TransactionValue From RecentTransactions) AS T
ON CA.CustomerId = T.CustomerId
WHEN NOT MATCHED THEN
    INSERT (CustomerId, Balance)
    VALUES (T.CustomerId, T.TransactionValue)
WHEN MATCHED THEN
    UPDATE SET Balance = Balance + TransactionValue;

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don’t allow entries that have zero stock.

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
    WHEN NOT MATCHED THEN
    INSERT VALUES(s.winename, s.stock_delta) WHERE s.stock_delta > 0
WHEN MATCHED THEN
    UPDATE SET stock = w.stock + s.stock_delta WHERE w.stock + s.stock_delta > 0;

The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.