INTO clause   Use this clause to define the target object for the MERGE statement. target-object can be the name of a base table, regular view, or derived table; it cannot be a materialized view. The derived table or view must represent an updatable query block. For example, if the view or derived table definition contains UNION, INTERSECT, EXCEPT, or GROUP BY, then it cannot be used as a target object for the MERGE statement.

When target-object is a derived table, the optional into-column-list can be used to provide alternate names for the columns of the derived table. When used in this manner, the size of the into-column-list must match the column list for the derived table, and the ordering of the two lists must be the same.

When target-object is a base table or view, into-column-list can be used to specify a subset of the table or view columns as relevant for the rest of the MERGE statement.

The database server uses into-column-list to resolve:

If you do not specify into-column-list, then into-column-list is assumed to contain all the columns of the target-object.

USING clause   Use this clause to define the source of the data you are merging from. source-object can be a base table (including table hints), a view, a materialized view, a derived table, or a procedure. If source-object is a derived table, you can specify using-column-list. All columns of source-object are used if you do not specify using-column-list.

WITH AUTO NAME clause   Use this clause to get the server to automatically use column names to match columns in the into-column-list columns in target-object for the merge operation. The following examples are equivalent and demonstrate that the order of the columns in into-column-list changes to match the names of the columns in the source-object when WITH AUTO Name is specified:

... INTO T ( Name, ID, Description ) USING WITH AUTO NAME ( SELECT Description, Name, ID FROM PRODUCTS WHERE Description LIKE '%cap%') ... INTO T ( Description, Name, ID ) USING ( SELECT Description, Name, ID FROM PRODUCTS WHERE Description LIKE '%cap%' )

ON clause   Use this clause to specify the condition to match a row in source-object with rows in target-object.

For more information about search condition syntax, see Search conditions.

You can specify ON PRIMARY KEY to match source-object rows based on the target-object primary key definition. source-object does not need a primary key. However, target-object must have a primary key. When specifying ON PRIMARY KEY:

WHEN MATCHED and WHEN NOT MATCHED clauses   Use the WHEN MATCHED and WHEN NOT MATCHED clauses to define an action to take when a row from source-object matches or does not match a row in target-object. You specify the action after the THEN keyword. You can control the actions to take for subsets of matching or non-matching rows by specifying an additional AND clause.

The ON clause determines how rows from source-object are separated into matching and non-matching rows. A row in source-object is considered a matching row when the ON clause is TRUE for at least one row in target-object. A row from source-object is considered a non-matching row when the ON clause is not TRUE for any rows in target-object. Use multiple WHEN MATCHED and WHEN NOT MATCHED clauses to partition sets of matching and non-matching rows into disjoint subsets. Each subset is processed by a WHEN clause. WHEN MATCHED and WHEN NOT MATCHED clauses are processed in the order they appear in the MERGE statement.

The search condition specified in the AND clause of a WHEN MATCHED or WHEN NOT MATCHED clause determines if a candidate row is processed by the specific clause. When you specify a WHEN MATCHED or WHEN NOT MATCHED clause without the AND clause the search condition in the AND clause is assumed to be TRUE. If a row satisfies the AND condition for more than one clause, the row is processed by the clause that appears first in the MERGE statement.

An error is returned when any of the WHEN MATCHED clauses process the same target-object row more than once. A target-object row can belong to the same subset of the same WHEN MATCHED clause more than once if it matches two different input rows from the source-object.

In the following example an error is returned because the row with ID 300 from the target-object Products matches 111 rows from the source-object SalesOrderItems. All the matches belong to the same subset corresponding to the WHEN MATCHED THEN UPDATE clause.

MERGE INTO Products 
   USING SalesOrderItems S
   ON S.ProductID = Products.ID
   WHEN MATCHED THEN UPDATE SET Products.Quantity = 20;

WHEN MATCHED: For a matching row, you can specify one of the following actions for match-action:

WHEN NOT MATCHED: For a non-matching row, you can specify one of the following actions for non-match-action:

OPTION clause   Use this clause to specify hints for executing the statement. The following hints are supported:

• MATERIALIZED VIEW OPTIMIZATION option-value
• FORCE OPTIMIZATION
• option-name = option-value

For a description of these options, see the OPTIONS clause of the SELECT statement.

SQL/2003   The MERGE statement is feature F312 of the SQL/2003 standard. The MERGE statement in SQL Anywhere is compliant with the MERGE statement specification in the SQL/2003 standard, with additional extensions. Some of these extensions to the MERGE statement are compliant with the forthcoming SQL/2008 standard. These include: