Merges tables, views, and procedure results into a table or view.
SyntaxMERGE 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
ParametersINTO 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 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:
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 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 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:
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.
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.
OPTION clause Use this clause to specify hints for executing the statement. The following hints are supported:
OPTION( isolation_level = ... ) specification in the query text overrides all other means of specifying isolation level for a query.
For a description of these options, see the OPTIONS clause of the SELECT statement.
RemarksRows 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 Import data with the MERGE statement.
PermissionsDBA 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 effectsAny triggers defined for target-object are fired.
See also Standards and compatibilitySQL/2008 The MERGE statement comprises features F312 and F313 of the SQL/2008 standard. The MERGE statement in SQL Anywhere is compliant with the MERGE statement specification in the SQL/2008 standard, with additional extensions. The SQL Anywhere-specific extensions to the MERGE statement include:
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
ExamplesThe 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 more detailed examples of the MERGE statement, see Import data with the MERGE statement.
 |
Discuss this page in DocCommentXchange.
|
Copyright © 2010, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.0 |