MERGE Syntax
Syntax
MERGE INTO table [ [AS] corr ]
[using-clause]
on-clause
{[when-matched-clause] | [when-not-matched-clause]}
[[FOR] access-option ACCESS]
[WITH control-query-default control-list]
[statement-level-hint-list]
using-clause is:
USING (select-statement) [AS] corr
[col-expr-list]
on-clause is:
ON search-condition
when-matched-clause is:
WHEN MATCHED THEN UPDATE SET set-clause [, set-clause]...
set-clause is:
column-name = {expression||NULL}
when-not-matched-clause is:
WHEN NOT MATCHED THEN INSERT insert-values-list
insert-values-list is:
[(column1, ..., columnN)] VALUES (value1, ..., valueN)
access-option is:
READ COMMITTED
control-query-default is:
CONTROL QUERY DEFAULT
control-list is:
attribute 'attr-value'
| attribute 'attr-value' [, control-list]...
statement-level-hint-list is:
USING <<+ hint-list >>
hint-list is:
hint [, hint-list]
hint is:
statement-hint
| table-hint
statement-hint is:
join-type-hint
table-hint is:
access-path-hint
| cardinality-hint
| selectivity-hint
join-type-hint is:
USE_HASHJOIN (table-1 [,table-2])
| NO_HASHJOIN (table-1 [,table-2])
| USE_MERGEJOIN (table-1 [,table-2])
| NO_MERGEJOIN (table-1 [,table-2])
| USE_NESTEDJOIN (table-1 [,table-2])
| NO_NESTEDJOIN (table-1 [,table-2])
access-path-hint is:
BASETABLE table
| NO_BASETABLE table
| INDEX table( index-list )
| NO_INDEX table( index-list )
| INDEXJOIN table( index-list )
| NO_INDEXJOIN table( index-list )
index-list is:
index [, index]...
cardinality-hint is:
CARDINALITY table( cardinality-value )
selectivity-hint is:
SELECTIVITY table( selectivity-value )Parameters
table- Is the target table on which the MERGE operation is performed. The table name must be an ANSI logical name.
corr-
Is the correlation name of the table.
using-clause-
Is a SELECT statement, the output of which is used as a source for the MERGE statement.
The SELECT statement:-
Can have a GROUP BY clause.
-
Cannot have an ORDER BY clause.
-
Cannot be an EMBEDDED UPDATE or DELETE statement.
-
Cannot use STREAM ACCESS or SET ON ROLLBACK clause.
The correlation name for the subquery specified in the USING clause is mandatory.
-
col-expr-list-
Is a list of derived columns.
search-condition-
Is the search condition upon which the MERGE statement either updates or inserts rows. If an INSERT clause is specified, the search condition must be a predicate on the clustering key of the table. If there is no INSERT clause in the MERGE statement, the search condition can be on nonkey columns.
-
Predicates are supported on columns of all data types and character sets. Expressions are also supported in predicates.
-
Predicates on SYSKEY are not allowed in the ON clause of a MERGE statement.
-
when-matched-clause-
Updates one or more columns in the target table when the search-condition in the ON clause becomes TRUE. The WHEN MATCHED clause is also referred to as UPDATE clause.
WHERE or DELETE clause cannot be specified for the WHEN MATCHED clause.
set-clause-
Associates a value with a specific column in the target table and that column cannot be part of clustering key. For each set-clause, the value of the update source expression (or NULL) replaces the value of the specified target column-name. The data type of each target column must be compatible with the data type of its source value.
NOTE:The SET clause of the UPDATE statement cannot contain a subquery.
when-not-matched-clause-
Inserts one or more records into the target table when the ON condition becomes FALSE. If the column list after the INSERT keyword is not specified, then the number of columns in the target table must match the number of values in the VALUES clause. The WHEN NOT MATCHED clause is also referred to as INSERT clause.
NOTE:The key value specified in the ON clause and the VALUES clause must be the same.
The MERGE statement can include optimizer hints and inline CQD. For optimizer hints and inline CQD parameter description, see the SQL/MX 3.4 Reference Manual.
Considerations
MERGE and MATCHED are ANSI reserved keywords.Privileges required
-
SELECT privilege on the source tables.
-
INSERT and UPDATE privileges on the target table.
Transaction initiation and termination
-
Since MERGE is a single statement, both the UPDATE and the INSERT operate under the same transaction. If either of the operation fails, both the operations are rolled back when AUTOCOMMIT is ON.
-
If the MERGE statement fails within a user transaction, the entire transaction is aborted.
Reporting
When a MERGE statement is executed successfully, SQL/MX does not report the number of inserted and updated rows separately, but reports the sum as merged rows. In case the same row is updated more than once, each update instance is added to the total number of merged rows.
On predicate
If there is an INSERT clause, then the ON predicate must refer to all the clustering key columns from the target table, so that only one row is selected. Consider the following examples:
create table table1 (a1 int not null, a2 int not null, a3 int not null, a4 int, primary key (a1,a2,a3));
create table table2 (a1 int not null, a2 int, a3 int, a4 int, primary key (a1));
insert into table2 values (1,2,3), (4,5,6);
merge into table1 A using (select * from table2) B ON (A.a1=B.a1 and A.a2=B.a2 and A.a3=B.a3) when not matched then insert values (B.a1, B.a2, B.a3, 8); The MERGE statement inserts tuples (1, 2, 3, 8) and (4, 5, 6, 8) into table1 since all primary key columns were specified in the ON clause. However, the following statement is missing primary key column a3 in the ON predicate and the SQL/MX compiler will return an error:
merge into table1 A using (select * from table2) B ON (A.a1=B.a1 and A.a2=B.a2)
when not matched then insert values (B.a1, B.a2, B.a3, 8);
*** ERROR[4504] Non-unique ON clause cannot be specified with an INSERT clause.
*** ERROR[8822] The statement was not prepared.In a table where primary key constraint is added using the ALTER TABLE statement and not during the creation of the table, the clustering key is the SYSKEY. It is not the
key-column-list specified in the primary key constraint. For such tables, the ON clause is always nonunique even if all the primary key columns have equality predicates defined on them. Therefore, MERGE with INSERT clause cannot be issued on tables with added primary key constraints.
The MERGE statement does a join of source rows and rows in the destination table. If the ON predicate selects more than one row, the destination row may be updated more than once, as the following example illustrates.
Example
create table tabOne (a int , b int, c int not null, d int default 50 not null, primary key (c,d));
create index IndexOneTabOne on tabOne(a) ;
create index IndexTwoTabOne on tabOne(b) ;
create table tabTwo (a int , b int, c int not null, d int default 50 not null, primary key (c,d));
create index IndexOneTabTwo on tabTwo(c);
insert into tabOne Values (10,20,30,40);
insert into tabOne Values (10,20,30,50);
insert into tabOne Values (10,20,40,50);
insert into tabTwo Values (10,200,30,40);
insert into tabTwo Values (10,200,30,50);
insert into tabTwo Values (10,200,40,50);
insert into tabTwo Values (10,200,100,100);
insert into tabTwo Values (10,200,200,200);
>>select * from tabOne;
A B C D
----------- ----------- ----------- -----------
10 20 30 40
10 20 30 50
10 20 40 50
--- 3 row(s) selected.
>> MERGE INTO tabOne
+> USING (select * from tabTwo) tabTwo
+> ON (tabOne.c=tabOne.c AND tabOne.d=tabOne.d)
+> WHEN MATCHED THEN UPDATE SET a=tabOne.a*10;
--- 15 row(s) merged.
>>select * from tabOne;
A B C D
----------- ----------- ----------- -----------
1000000 20 30 40
1000000 20 30 50
1000000 20 40 50
--- 3 row(s) selected.Optimizer hints and inline CQDs
-
All optimizer hints and Inline CQDs, for both the MERGE statement and the subquery specified in the USING clause, must be specified at the end of the MERGE statement.
-
Table hints for individual table references in the subquery specified in the USING clause can be specified beside the table reference.
Data type conversion
-
The constants, dynamic parameters, and host variables specified in the ON clause predicates are not converted to the target column data type for conversion.
-
The constants, dynamic parameters, and host variables specified in the UPDATE and INSERT clauses of the MERGE statement are converted to the target column data type before the values are updated or inserted.
Examples
>>select * from employee;
--- 0 row(s) selected.
>> MERGE INTO employee
+> ON empnum = 120
+> WHEN MATCHED THEN UPDATE SET jobcode = 1000
+> WHEN NOT MATCHED THEN
+> INSERT VALUES (120.54, 'TIM', 'WALKER', 3000, 1000, 32000.00);
--- 1 row(s) merged.
>>select * from employee;
Employee/Number First Name Last Name Dept/Num Job/Code Salary
--------------- --------------- -------------------- -------- -------- -----------
120 TIM WALKER 3000 1000 32000.00 >> MERGE INTO employee
+> ON empnum = 120
+> WHEN MATCHED THEN UPDATE SET jobcode = 1234.54;
--- 1 row(s) merged.
>> select * from employee;
Employee/Number First Name Last Name Dept/Num Job/Code Salary
--------------- --------------- -------------------- -------- -------- -----------
120 TIM WALKER 3000 1234 32000.00
--- 1 row(s) selected.
>>MERGE INTO employee
+> ON empnum = 120.54
+> WHEN MATCHED THEN UPDATE SET jobcode = 1000
+> WHEN NOT MATCHED THEN
+> INSERT VALUES (120.54, 'TIM', 'WALKER', 3000, 1000, 32000.00);
*** ERROR[8102] The operation is prevented by a primary key EMPLOYEE_172678469_2978 on table UPSERT.SCH.EMPLOYEE.
--- 0 row(s) merged.Restrictions
For the restrictions, consider tables tabOne and tabTwo with the values specified in the example of ON Predicate section.
-
The target table of a MERGE statement cannot be:
-
Tables with triggers, RI constraints, Materialized Views, user-defined identity columns, or sequence generator objects.
-
Derived tables such as views and subqueries.
-
MP tables and MP aliases.
-
-
Sequence generator objects cannot be referenced in any of the following clauses of the MERGE statement: USING, ON, UPDATE, or INSERT.
-
SET ON ROLLBACK clause is not allowed in a MERGE statement.
-
The key value specified in the ON clause and the VALUE clause must be the same. The following statement is not permitted:
MERGE INTO tabOne ON c = 30 and d = 40 WHEN NOT MATCHED THEN INSERT VALUES (10, 20, 40, 50); -
The ON clause cannot contain a subquery. The following statement is not permitted:
MERGE INTO tabOne ON c = (SELECT a FROM tabTwo) WHEN... -
The UPDATE SET clause in a MERGE statement cannot contain a subquery. The following statement is not permitted:
MERGE INTO tabOne ON c = 30 and d = 40 WHEN MATCHED THEN UPDATE SET b = (select b from tabTwo); -
WHERE condition is not supported in UPDATE and INSERT clauses. The following statement is not supported:
>> MERGE INTO tabOne c = 30 and d = 40 +> WHEN MATCHED THEN UPDATE SET b = 2 WHERE b > 0 +> WHEN NOT MATCHED THEN INSERT VALUES (1, 2); *** ERROR [15001] A syntax error occurred at or before: MERGE INTO tabOne c = 30 and d = 40 WHEN MATCHED THEN UPDATE SET b = 2 WHERE b > 0 WHEN NOT ^ (63 characters from start of SQL statement) *** ERROR [8822] The statement was not prepared.>> MERGE INTO tabOne c = 30 and d = 40
-
The constant filter predicate in the ON clause is not supported. The following statement is not supported:
>> MERGE INTO tabOne ON 0 = 1 -- constant filter predicate +> WHEN MATCHED THEN UPDATE SET b = 2 WHERE b > 0 +> WHEN NOT MATCHED THEN INSERT VALUES (1, 2); *** ERROR [3241] This MERGE statement is not supported. Reason: Non-unique ON clause not allowed with INSERT. -
The INSERT VALUES clause in a MERGE statement cannot contain a subquery. The following statement is not permitted:
MERGE INTO tabOne ON c = 30 and d = 40 WHEN MATCHED THEN INSERT values (10, select 20 from tabTwo, 30, 40); -
The column being updated cannot be a part of the clustering key.
-
MFC, ROWSETS, and non-atomic ROWSETS are not supported for MERGE statement.
-
MERGE is not supported with embedded UPDATE or DELETE, or with stream access.
-
DP2 savepoints are not enabled with MERGE statement.
Example MERGE Statements
Inserts a new row (1177, 'ARROTECH', '192 16TH ST.', 'BANGALORE', 'KARNATAKA', '560048', 'F2') if the row with custnum = 1177 does not exist.
MERGE INTO customer ON custnum = 1177
WHEN NOT MATCHED THEN
INSERT VALUES( 1177, 'ARROTECH', '192 16TH ST.',
'BANGALORE', 'KARNATAKA', '560048', 'F2');
--- 1 row(s) merged.
>>select * from customer;
Cust/Num Customer Name Street City State Post Code CR
-------- ------------------ ---------------------- -------------- ------------ ---------- --
1177 ARROTECH 192 16TH ST. BANGALORE KARNATAKA 560048 F2
--- 1 row(s) selected.
Updates the column custname to ‘ARROTECH’ if the row with key custnum = 1177 exists. If the row does not exist, a new row (1177, 'ARROTECH', '192 16TH ST.', 'BANGALORE', 'KARNATAKA', '560048', 'F2') is inserted.
MERGE INTO customer ON custnum = 1177
WHEN MATCHED THEN UPDATE SET custname = 'ARROTECH'
WHEN NOT MATCHED THEN
INSERT VALUES (1177, 'ARROTECH', '192 16TH ST.’,
'BANGALORE', 'KARNATAKA', '560048', 'F2');
--- 1 row(s) merged.
If the row with custnum = 1177 exists, the following MERGE statement updates the column custname to ‘ARRO’ :
>>select * from customer;
Cust/Num Customer Name Street City State Post Code CR
-------- ------------------ ---------------------- -------------- ------------ ---------- --
1177 ARROTECH 192 16TH ST. BANGALORE KARNATAKA 560048 F2
--- 1 row(s) selected.
MERGE INTO customer ON custnum = 1177
WHEN MATCHED THEN UPDATE SET custname = 'ARRO';
--- 1 row(s) merged.
>>select * from customer;
Cust/Num Customer Name Street City State Post Code CR
-------- ------------------ ---------------------- -------------- ------------ ---------- --
1177 ARRO 192 16TH ST. BANGALORE KARNATAKA 560048 F2
--- 1 row(s) selected.
If the row with the empnum = 120 does not exist, the following MERGE statement inserts a new row (120, 'TIM', 'WALKER', 3000, job.jobcode, 32000.00) into the employee table. If a row with the empnum = 120 exists, the jobcode of the row is merged with the jobcode from the job table.
MERGE INTO employee USING (SELECT jobcode FROM job) job
ON empnum = 120
WHEN MATCHED THEN UPDATE SET jobcode = job.jobcode
WHEN NOT MATCHED THEN
INSERT VALUES (120, 'TIM', 'WALKER', 3000, job.jobcode, 32000.00);
--- 1 row(s) merged.
The following MERGE statement uses the dynamic parameters in the INSERT clause. The MERGE statement compiles successfully. However, based on the value of the dynamic parameter, the statement might or might not execute successfully.
>>prepare xx from
+>MERGE INTO employee
+>ON empnum = ?
+>WHEN MATCHED THEN UPDATE SET jobcode = 1000
+>WHEN NOT MATCHED THEN
+>INSERT VALUES (?, 'TIM', 'WALKER', 3000, 1000, 32000.00);
--- SQL command prepared.
>>
>>execute xx using 121,120;
*** ERROR[8500] Key values specified in the INSERT part of a MERGE statement must be the same as those specified in the ON clause.
--- 0 row(s) merged.
>>
>>execute xx using 120,121;
*** ERROR[8500] Key values specified in the INSERT part of a MERGE statement must be the same as those specified in the ON clause.
--- 0 row(s) merged.
>>
>>execute xx using 120,120;
--- 1 row(s) merged.C example
EXEC SQL MERGE INTO employee
USING (SELECT jobcode into :outparam1 FROM job) job
ON empnum = 120
WHEN MATCHED THEN UPDATE SET jobcode = job.jobcode
WHEN NOT MATCHED THEN
INSERT VALUES (120, 'TIM', 'WALKER', 3000, 300, 32000.00);
END-EXEC;
COBOL example
EXEC SQL MERGE INTO employee
USING (SELECT jobcode into :outparam1 FROM job) job
ON empnum = 120
WHEN MATCHED THEN UPDATE SET jobcode = job.jobcode
WHEN NOT MATCHED THEN
INSERT VALUES (120, 'TIM', 'WALKER', 3000, 300, 32000.00);
END-EXEC;