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.
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)
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 Adaptive 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.
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.
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”.
are well-formed Boolean expressions used in the matched/not matched clauses.
both options are always in the matched clause. update assigns new values to the matching row, while delete removes the current matching row.
always appears in the not matched clause, and inserts the nonmatching row in the target table.
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
Uses a derived table as the source table with dynamic parameter markers:
merge into GlobalSales (Item_number, Description, Quantity)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
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.
Has more than one matching row in the target table – an error is raised. This is standard SQL-2003 behavior.
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.
Any user who has select permission on the source object, and insert, update, or delete permission on the target object can use merge.
