You use MERGE to unite the INSERT and UPDATE functions in one operation. Depending on the result of the constraint in the ON clause, MERGE updates column values of rows which already exist (WHEN MATCHED THEN) or inserts new rows into an existing table (WHEN NOT MATCHED THEN).
This constraint can range from a simple existence query to complex search criteria. Trivial constraint (e.g. 1 <> 1) are also possible; they lead to it only being possible to update or insert rows.
The special literals (see "Special literals") which occur in the INSERT statement (and in preset values) and the time functions CURRENT_DATE, CURRENT_TIME and CURRENT_TIMESTAMP are evaluated once, and the calculated values apply for all inserts.
If integrity constraints are defined for the table or the columns concerned, these are checked after the insertion or update operation. If an integrity constraint is violated, the inserts and updates are undone and a corresponding
SQLSTATE is set.
Specific requirements must be satisfied to execute the MERGE statement:
To insert or update rows in table you must
be the owner of table or
at least have the INSERT privilege when insert_row is specified or
at least have the UPDATE privilege for all columns which are updated in update_row when update_row is specified.
You must also have the SELECT privilege for all tables
which are addressed in table_specification.The transaction mode of the current transaction must be READ WRITE.
MERGE INTO table [[AS] correlation_name ]
USING table_specification
ON search_condition
{ WHEN MATCHED THEN update_row |
WHEN NOT MATCHED THEN insert_row }...
update_row ::= UPDATE SET column = column-value [, column-value ]...
column-value ::= { expression | DEFAULT | NULL}
insert_row ::= INSERT [( column ,...)][COUNT INTO column ] VALUES( value [ ,value ] ... )
value ::= { expression | NULL | DEFAULT | * }
table
Name of the destination table into which rows are to be inserted or in which rows are to be updated. The destination table can be a base table or an updatable view. It may not contain any multiple columns (see note on "MERGE - Insert rows in a table or update column values").
correlation_name
Table name used in the statement as a new name for the table table.
The correlation_name must be used to qualify the column name in every column specification that references the table table if the column name is not unambiguous.
The new name must be unique, i.e. correlation_name can only occur once in a table specification of this statement.
You must give a table a new name if the columns in the table cannot be identified otherwise uniquely.
In addition, you may give a table a new name in order to formulate an expression so that it is more easily understood or to abbreviate long names.
USING table_specification
Specifies a source table (different from the destination table table) which is to be used to insert rows into the destination table table or update rows in the destination table table. It may not contain any multiple columns (see note on "MERGE - Insert rows in a table or update column values").
The destination table table may also be referenced in the table_specification.
ON search_condition
Specifies the condition which decides whether the UPDATE clause is to be executed (result: TRUE) or whether the INSERT clause is to be executed (result: FALSE).
More precisely, each row of the source table is checked to see whether there is a row in the destination table so that the search_condition is true for the combination of these two rows.
If no such row exists in the destination table, the INSERT clause is executed, i.e. the row in the source table is inserted in the destination table.
If one or more such rows exist in the destination table, the UPDATE clause is executed for each of these rows, i.e. the corresponding rows in the destination table are updated. Two different rows in the source table may not lead to updates in the destination table (multiple update), otherwise the MERGE statement is aborted with SQLSTATE.
WHEN MATCHED THEN update_row
A row which is to be updated was found.
UPDATE SET ...
Information for updating the row which is to be updated.
For a description of the column, expression, DEFAULT and NULL parameters, see the corresponding descriptions in the SQL statement UPDATE on "UPDATE - Update column values".A row which is to be updated may only be updated once. Any further attempt to update it is rejected with SQLSTATE.
The primary key value in a partitioned table may not be modified.
WHEN NOT MATCHED THEN insert_row
No corresponding row was found. The new row is to be inserted.
INSERT ...
Information for inserting the new row.
(column,...)
Lists the columns, for which the INSERT clause of the MERGE statement specifies the values and stipulates the order for this. The values of the remaining columns in the row to be inserted are not specified in the MERGE statement; they are DEFAULT or NULL values or values defined by SESAM/SQL.
For a description of the column parameter, see the corresponding description in the SQL statement INSERT on "INSERT - Insert rows in table".
No column_list specified:
The MERGE statement specifies the values in the row to be inserted for each column of the target table table (except for the column specified by COUNT INTO), in the order specified with CREATE TABLE and ALTER TABLE or with CREATE VIEW.
COUNT INTO column
See the corresponding description in the SQL statement INSERT on "INSERT - Insert rows in table".
VALUES (...)
The required column values are specified for the row which is to be inserted.
For a description of expression, NULL, DEFAULT and *, see the description of insert_expression_1 in the SQL statement INSERT on "INSERT - Insert rows in table".
In expression you may not specify a table which references the destination table into which the new rows are to be inserted.
In particular you may not reference any column of the destination table.
The number of columns of the VALUES clause must equal the number of column values to be specified for each inserted row, as specified with (column,...) and COUNT INTO. The nth column of the destination table contains the values for the nth column specification in (column,...) (if (column,...) is specified), or for the nth column of table, where any column of table introduced with COUNT INTO is not counted.
The assignment rules specified in section "Entering values in table columns" apply to these assignments.
Any remaining columns of the inserted rows are set as follows:
The column specified by COUNT INTO is set to a value defined by SESAM/SQL.
Columns with a default value are set to the default value (DEFAULT).
Columns without a default value are set to the NULL value.
If the target table table is a view, the rows will be inserted into the underlying base table; columns of the base table not contained in the view will be set in the same way.
Inserting values for multiple columns
Base tables with multiple columns cannot be processed directly in the MERGE statement.
MERGE and integrity constraints
By specifying integrity constraints when you define the base table, you can restrict the range of values for the corresponding columns. The value specified in the MERGE statement must satisfy the defined integrity constraint, otherwise the MERGE statement is aborted with SQLSTATE.
MERGE and transaction management
MERGE initiates an SQL transaction outside routines if no transaction is open. If you define an isolation level, you can control how the MERGE statement can be affected by concurrent transactions (see section "SET TRANSACTION - Define transaction attributes").
If an error occurs during the MERGE statement, any updates already performed by the MERGE statement are canceled.
Examples
The example below concerns inventory management when a new delivery arrives. In the case of the existiing articles with the same price the inventory in the base table is updated. New articles in the delivery table are added to the inventory table.
MERGE INTO inventory AS b USING delivery AS l
ON b.article_no = l.article_no AND b.article_price = l.article_price
WHEN MATCHED THEN
UPDATE SET article_quant = b.article_quant + l.article_quant
WHEN NOT MATCHED THEN
INSERT (article_no,article_price,article_quant)
VALUES (l.article_no,l.article_price,l.article_quant)
The complex example below also concerns inventory management when a new delivery arrives. The data for the new delivery is, for example, supplied in the CSV input file DELIVERY.DATA (with a header):
Article number,Quantity,New price 1, 4, 18.50 2, 11, 19.90 3, 0, 22.95 4, 3, 84.30 5, 7, 25.90
The MERGE statement below updates the inventory table for the articles which already exist. New articles in the delivery are added to the inventory table. The header of the CSV file is skipped by means of the WITH ORDINALITY clause in conjunction with WHERE.
MERGE INTO inventory
USING (SELECT CAST(article number as INT),
CAST(quantity as INT),
CAST(new price as NUMERIC(10,2))
FROM TABLE(CSV('DELIVERY.DATA'
DELIMITER ',' QUOTE '"' ESCAPE '\',
varchar(30), varchar(40), varchar(50)))
WITH ORDINALITY
AS T(article number, quantity, new price, counter)
WHERE counter > 1)
AS delivery(article number, quantity, new price)
ON inventory.article number= delivery.article number
WHEN MATCHED THEN UPDATE SET
quantity = inventory.quantity + delivery.quantity,
price = delivery.new price
WHEN NOT MATCHED THEN INSERT (article number, quantity, price)
VALUES(delivery.article number,
delivery.quantity, delivery.new price)
See also
DELETE, INSERT, UPDATE