MERGE statement

Use this statement to merge tables, views, and system procedure results into a table or view.

Syntax 
MERGE
INTO target-object [ into-column-list ]
USING [ WITH AUTO NAME ] source-object
   ON merge-search-condition
merge-operation […]
 [ OPTION ( query-hint, ... ) ]

target-object: 
[ userid.]target-table-name [ [ AS ] target-correlation-name ]  
| [ userid.]target-view-name [ [ AS ] target-correlation-name ] 
| ( select-statement ) [ AS ] target-correlation-name 

source-object : 
[ userid.]source-table-name [ [ AS ] source-correlation-name ] [ WITH ( table-hints ) ]
| [ userid.]source-view-name [ [ AS ] source-correlation-name ] 
| [ userid.]source-mat-view-name [ [ AS ] source-correlation-name ] 
| ( select-statement ) [ AS ] source-correlation-name [  using-column-list  ]
| procedure

procedure :
[ owner.]procedure-name ( procedure-syntax )
   [ WITH ( column-name data-type, ... ) ]
   [ [ AS ] source-correlation-name ]

merge-search-condition :
search-condition
| PRIMARY KEY

merge-operation :
WHEN MATCHED [ AND search-condition ] THEN match-action
| WHEN NOT MATCHED [ AND search-condition ] THEN not-match-action

match-action :
DELETE
| RAISERROR [ error-number ]
| SKIP 
| UPDATE SET set-item, ...  
| UPDATE [ DEFAULTS { ON | OFF } ]

not-match-action :
INSERT 
| INSERT [ insert-column-list ] VALUES ( value, ... ) 
| RAISERROR [ error-number ]
| SKIP

set-item :
[target-correlation-name.]column-name = { expression | DEFAULT }
| [ owner-name.]target-table-name.column-name = { expression | DEFAULT }

insert-column-list : 
( column-name, ... )

query-hint :
MATERIALIZED VIEW OPTIMIZATION option-value
| FORCE OPTIMIZATION
| option-name = option-value

into-column-list :
( column-name, ... )

using-column-list :
( column-name, ... )

error-number : positive integer or variable greater than 17000

option-name : identifier

option-value : hostvar (indicator allowed), string, identifier, or number

table-hints : see FROM clause

search-condition : see Search conditions

set-clause-list : see SET statement

Parameters

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

    • UPDATE without a SET clause in WHEN MATCHED clause
    • INSERT without a VALUES clause in a WHEN NOT MATCHED clause
    • PRIMARY KEY search condition in the ON clause
    • WITH AUTO NAME clause in the USING clause

    If you do not specify into-column-list, then into-column-list is assumed to contain all of 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 sever 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 on 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:

    • An error is returned if target-object is not a base table, or if it does not have a primary key.
    • An error is returned if one or more primary key columns are not included in into-column-list.
    • The number of columns in into-column-list and using-column-list can be different as long as every primary key column in into-column-list has a corresponding matching column in using-column-list. For example, if into-column-list is (I1, I2, I3), using-column-list is (U1, U2), and the primary key columns are (I2, I3), an error is returned because column (I3) of the target-object primary key does not have a match in the using-column-list.
    • Regardless of the definition of the primary key, matching of primary key columns in into-column-list to expressions in using-column-list is based on the position of the primary key columns in into-column-list. For example, suppose the primary key on target-object is defined as (B, C), and the into-column-list is (E, C, F, A, D, B). When ON PRIMARY KEY is specified, target-object column B is compared to the sixth element of using-column-list because column B is in the sixth position in the into-column-list. Likewise, target-object column C is compared to the second element of using-column-list.

      ON PRIMARY KEY is syntactic shorthand for a corresponding ON condition. For example, assume that into-column-list is (I1, I2, .. In), and that the corresponding matched using-column-list is (U1, U2, .. Um). Also assume that the primary key columns of target-object are I1, I2, I3 and all of the primary key columns are contained in into-column-list. In this case, merge-search-condition is defined as the conjunct "I1=U1 AND I2=U2 AND I3=U3".

  • 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 of 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:

    • DELETE   Specify DELETE to delete the row from target-object.

    • RAISERROR   Specify RAISERROR to terminate the merge operation, roll back any changes, and return an error. By default, when you specify RAISERROR, the database server returns SQLSTATE 23510 and SQLCODE -1254. Optionally, you can customize the SQLCODE that is returned by specifying the error-number parameter after the RAISERROR keyword. The custom SQLCODE must be a positive integer greater than 17000, and can be specified either as a number or a variable. When you specify a custom SQLCODE, the number returned is a negative number.

      For example, if you specify WHEN MATCHED AND search-condition THEN RAISERROR 17001, then, when a row is found that satisfies the conditions of the WHEN clause, the merge operation fails, changes are rolled back, and the error returned has SQLSTATE 23510 and SQLCODE -17001. See Using the RAISERROR action.

    • SKIP   Specify SKIP to skip the row; no action is taken.

    • UPDATE   Specify UPDATE SET to update the row using the set-item values. set-item is a simple assignment expression where a column is set to the value of expression. There are no restrictions on the expression. You can also specify DEFAULT to set the column to the default defined for the column.

      For example, UPDATE SET target-column1=DEFAULT, target-column2=source-column2 sets target-column1 to its default value and sets target-column2 to be the same as the modify row from source-column2 in source-object.

      If you do not specify the SET clause, the SET clause is defined by into-column-list and using-column-list. For example, if into-column-list is (I1, I2, .. In), and using-column-list is (U1, U2, .. Un) the SET clause is assumed to be "SET I1=U1 , I2=U2 , .. In=Un".

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

    • INSERT   Specify INSERT ... VALUES to insert the row using the specified values. When you specify the INSERT clause without a VALUES clause, the VALUES clause is defined by into-column-list and using-column-list. For example, if into-column-list is (I1, I2, .. In), and using-column-list is (U1, U2, .. Un), the INSERT without a VALUES clause is equivalent to INSERT (I1, I2, .. In) VALUES (U1, U2, .. Un).

    • RAISERROR   Specify RAISERROR to terminate the merge operation, roll back any changes, and return an error. When you specify RAISERROR, the database server returns SQLSTATE 23510 and SQLCODE -1254 by default. Optionally, you can customize the SQLCODE that is returned by specifying the error-number parameter after the RAISERROR keyword. The custom SQLCODE must be a positive integer greater than 17000, and can be specified either as a number or a variable. When you specify a custom SQLCODE, the number returned is a negative number.

      For example, if you specify WHEN NOT MATCHED AND search-condition THEN RAISERROR 17001, then, when a row is found that satisfies the conditions of the WHEN clause, the merge operation fails, changes are rolled back, and the error returned has SQLSTATE 23510 and SQLCODE -17001. See Using the RAISERROR action.

    • SKIP   Specify SKIP to skip the row; no action is taken.

  • OPTION clause   Use this clause to specify hints for executing the query. The following hints are supported.

    • MATERIALIZED VIEW OPTIMIZATION 'option-value'   Use the MATERIALIZED VIEW OPTIMIZATION clause to specify how the optimizer should make use of materialized views when processing the query. The specified option-value overrides the materialized_view_optimization database option for this query only. Possible values for option-value are the same values available for the materialized_view_optimization database option. See materialized_view_optimization option [database], and Improving performance with materialized views.

    • FORCE OPTIMIZATION   When a query specification contains only simple queries (single-block, single-table queries that contain equality conditions in the WHERE clause that uniquely identify a specific row), the query typically bypasses cost-based optimization during processing. You may want cost-based optimization to occur. For example, if you want materialized views to be considered during query processing, view matching must occur. However, view matching only occurs during cost-based optimization. If you want cost-based optimization to occur for a query, but your query specification contains only simple queries, specify the FORCE OPTIMIZATION option to ensure that the optimizer performs cost-based optimization when querying target-object. Similarly, specifying the FORCE OPTIMIZATION option in a SELECT statement inside of a procedure forces the use of the optimizer for any call to the procedure. In this case, plans for the statement are not cached.

      For more information about simple queries and view matching, see Phases of query processing.

    • option-name = option-value   Specify an option setting that takes precedence over any public or temporary option settings that are in effect, for this statement only. The supported options are:

Remarks

Rows in source-object are compared to rows in target-object and found to be matching or non-matching depending on whether they satisfy the conditions of the ON clause. Rows in source-object are considered a match if there exists at least one row in target-table such that merge-search-condition evaluates to true. Matching rows and non-matching rows are then grouped by the actions defined for them in the WHEN MATCHED and WHEN NOT MATCHED clauses according to the search conditions specified by the AND clauses. The process of grouping rows by matched and non-matched actions is referred to as branching, and each group is referred to as a branch.

Once branching is complete, the database begins executing the action defined for the rows of the branch. Branches are processed in the order in which they occur, which matches the order in which the WHEN clauses occur in the statement. If, during branching, more than one row in source-object has an action defined for the same row in target-object, the merge operation fails and an error is returned. This prevents the merge operation from performing more than one action on any given row in target-object.

As branches are processed, the insert, update, and delete actions are recorded in the transaction log as their respective INSERT, UPDATE, and DELETE statements.

For information about how triggers can impact the merge operation, see Using the MERGE statement.

Permissions

DBA authority, or:

  • INSERT, UPDATE, and DELETE permissions on target-object if the INSERT, UPDATE or DELETE action is specified in the MERGE statement.
  • SELECT permission is required on any objects referenced in the MERGE statement.
  • EXECUTE permission is required on any procedure referenced in the MERGE statement.
Side effects

Any triggers defined for target-object are fired.

See also
Standards and compatibility

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

    • multiple WHEN MATCHED and WHEN NOT MATCHED clauses
    • [ AND search-condition ] with WHEN [NOT] MATCHED clauses
    • DELETE in a WHEN MATCHED clause
    • RAISERROR in a WHEN [NOT] MATCHED clause
    • SKIP in a WHEN [NOT] MATCHED clause
    • OPTION clause
    • PRIMARY KEY clause
    • DEFAULTS clause
    • INSERT clause without a VALUES clause
    • WITH AUTO NAME clause
    • UPDATE clause without the SET clause

Examples

The following example merges a row from a derived table into the Products table, effectively adding a new tee shirt with the same attributes as an existing tee shirt, but with a new color, quantity, and product identifier. In this example if the product with identification number 304 already exists in the Products table then the row is not inserted: 

MERGE INTO Products ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo ) 
   USING WITH AUTO NAME ( 
      SELECT 304 AS ID, 
             'Purple' AS Color, 
             100 AS Quantity, 
             Name, 
             Description, 
             Size, 
             UnitPrice, 
             Photo 
          FROM Products WHERE Products.ID = 300 ) AS DT
   ON PRIMARY KEY
   WHEN NOT MATCHED THEN INSERT;

The following example is equivalent to the previous, but does not use syntactic shorthand:

MERGE INTO Products ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo ) 
   USING (
      SELECT 304 AS ID, 
             'Purple' AS Color, 
             100 AS Quantity, 
             Name, 
             Description, 
             Size, 
             UnitPrice, 
             Photo 
          FROM Products WHERE Products.ID = 300 )
          AS DT ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo )
   ON ( Products.ID = DT.ID )
   WHEN NOT MATCHED 
      THEN INSERT ( ID, Name, Description, Size, Color, Quantity, UnitPrice, Photo )
      VALUES ( DT.ID, DT.Name, DT.Description, DT.Size, DT.Color, DT.Quantity, DT.UnitPrice, DT.Photo );

For a more detailed examples of the MERGE statement, see Using the MERGE statement.

Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0