merge

Transfers rows from a source table into a target table.

Inserts rows that are in the source and have no matching key columns in the target.
Updates rows with key columns that already exist in the target with the values from the source row.

Syntax

merge
	into [[database.]owner.]identifier [as table_alias]
	using [[database.]owner.]identifier [as table_alias] 
		| (select_query) as alias_name [column_list]
	on merge_search_condition
	[ when matched [and search_conditions ]
		then {update set {col_name = expression} | delete} ]
	[ when not matched [and search_conditions ]
		then insert [(column_list)] values (value_list)

Parameters

into [[database.]owner.]identifier [as table_alias] – specifies the target object as a table or updatable view as a table alias. The target can be a fully qualified name identifier or a short name identifier—does not include the database name and the owner name—and in which case the SAP ASE server uses the current database and the user or database owner.
You may also specify the table alias as an alternative to reference the target table.
using [[database.]owner.]identifier [as table_alias] | (select_query) as alias_name [column_list] – specifies the source object as a table, view, or derived table. When the source object is:
- A table or view – use [[database.]owner.]identifier.
- A derived table – reference it by the select query, in the form of an alias name and an optional column list that defines the derived table.
merge_search_conditions – checks whether the row in the source table matches the row in the target table, and consists of a list of predicates such as “col_name = col_name”.
search_conditions – are well-formed Boolean expressions used in the matched/not matched clauses.
update set {col_name = expression} | delete – both options are always in the matched clause. update assigns new values to the matching row, while delete removes the current matching row.
insert [(column_list)] values (value_list) – always appears in the not matched clause, and inserts the nonmatching row in the target table.

Examples

Example 1 – Merges the DailySales table into GlobalSales:

merge into GlobalSales
        (Item_number, Description, Quantity)as G
    using DailySales as D
    ON D.Item_number = G.Item_number
    when not matched
        then
        insert (Item_number, Description, Quantity )
     values (D.Item_number, D.Description, D.Quantity)
    when matched
        then update set
            G.Quantity = G.Quantity + D.Quantity

Example 2 – Uses a derived table as the source table with dynamic parameter markers:

merge into GlobalSales as G
     using (select ?, ?, ?) as
     D (Item_number, Description, Quantity)
     ON D.Item_number = G.Item_number
     when not matched
        then
        insert (Item_number, Description, Quantity )
     values (D.Item_number, D.Description, D.Quantity)
     when matched
        then update set
          G.Quantity = G.Quantity + D.Quantity

Usage

The target table cannot be part of any referential integrity constraint.
There are no specific optimization for merge queries with on clauses that reference constant Boolean expressions, such as (1=0) or (1=1) .
The target columns referenced in the on clause cannot be in the set clause of the update action.
Although you can invoke a merge statement from within a stored procedure, update and insert statements are not allowed within a scalar SQL function.
The target table cannot be an updatable view with instead of triggers.
The merge statements can be cached, and the literals in the set clause of the update action and in the insert value list in the insert action are the target of the literal parameterization process.
The target table cannot be a proxy table.
For each row in the source table, if the row:
- Has a matching row in the target table and the search condition is evaluated to true – execute the update in the target table, or the corresponding row in the target table.
- Has no matching row in the target table and the search condition is evaluated to true – insert the row in the target table.
The merge statement can have multiple when matched and when not matched clauses with different search conditions. The first when in the when clauses that has its condition satisfied runs the corresponding action; the rest are ignored.
For each row in the target table, merge generates an error if it finds more than one matching row in the source table.

Permissions

Any user who has select permission on the source object, and insert, update, or delete permission on the target object can use merge.