Example : MERGE
This example uses dynamically supplied values for an employee table row to update the table if the data matches an existing employee or insert the new row into the table if the data does not match an existing employee. Column empno is the unique primary index for the employee table.
USING (empno INTEGER,
name VARCHAR(50),
salary INTEGER)
MERGE INTO employee AS t
USING VALUES (:empno, :name, :salary) AS s(empno, name, salary)
ON t.empno=s.empno
WHEN MATCHED THEN UPDATE
SET salary=s.salary
WHEN NOT MATCHED THEN INSERT (empno, name, salary)
VALUES (s.empno, s.name, s.salary);
This example can also be coded as the following upsert form of the UPDATE statement. See “UPDATE (Upsert Form)” on page 495.
USING (empno INTEGER,
name VARCHAR(50),
salary INTEGER)
UPDATE employee
SET salary=:salary
WHERE empno=:empno
ELSE INSERT INTO employee
(empno, name, salary) VALUES ( :empno, :name, :salary);
Example : MERGE With Subquery
This example generalizes “Example 1: MERGE” by using a subquery for source_table_reference rather than an explicit value list.
USING (empno INTEGER,
salary INTEGER)
MERGE INTO employee AS t
USING (SELECT :empno, :salary, name
FROM names
WHERE empno=:empno) AS s(empno, salary, name)
ON t.empno=s.empno
WHEN MATCHED THEN UPDATE
SET salary=s.salary, name = s.name
WHEN NOT MATCHED THEN INSERT (empno, name, salary)
VALUES (s.empno, s.name, s.salary);
Example : Using the DEFAULT Function With MERGE
The following examples show the correct use of the DEFAULT function within the MERGE statement.
MERGE INTO emp
USING VALUES (100, 'cc', 200, 3333) AS emp1 (empnum, name,
deptno, sal)
ON emp1.empnum=emp.s_no
WHEN MATCHED THEN
UPDATE SET sal=DEFAULT
WHEN NOT MATCHED THEN
INSERT VALUES (emp1.empnum, emp1.name, emp1.deptno, emp1.sal);
MERGE INTO emp
USING VALUES (100, 'cc', 200, 3333) AS emp1 (empnum, name,
deptno, sal)
ON emp1.empnum=emp.s_no
WHEN MATCHED THEN
UPDATE SET sal=DEFAULT(emp.sal)
WHEN NOT MATCHED THEN
INSERT VALUES (emp1.empnum, emp1.name, emp1.deptno, emp1.sal);
Example : Logging MERGE Errors
The following MERGE request logs all error types, including data errors, referential integrity errors, and USI errors, with an error limit of 100 errors.
MERGE INTO tgttbl AS t1
USING (SELECT c1,c2,c3
FROM srctbl) AS t2
ON t1.c1=t2.c1
WHEN MATCHED THEN
UPDATE SET t1.c2=t2.c2 + t1.c2, t1.c3=t2.c3
WHEN NOT MATCHED THEN
INSERT INTO t1 VALUES (t2.c1, t2.c2, t2.c3)
LOGGING ERRORS WITH LIMIT OF 100;
Example : Using MERGE for Update and Insert Operations Within a Single SQL Request
The MERGE statement provides the ability to perform update and insert operations within a single SQL request. MERGE can also perform index and referential integrity maintenance in a single pass, unlike the case where update and insert operations must be executed separately.
For example, suppose you create the following tables and then use a MERGE request to update and insert rows from source table t2 into target table t1:
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER);
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER);
MERGE INTO t1
USING t2
ON a1=a2
WHEN MATCHED THEN
UPDATE
SET b1=b2
WHEN NOT MATCHED THEN
INSERT (a2, b2, c2);
An EXPLAIN shows a merge with matched updates and unmatched inserts into OB.t1 from OB.t2 with a condition of ("OB.t1.a1 = OB.t2.a2").
This MERGE request can also be coded as the following semantically equivalent multistatement UPDATE INSERT request:
UPDATE t1
FROM t2
SET b1=b2
WHERE a1=a2
;INSERT INTO t1
SELECT a2, b2, c2
FROM t2, t1
WHERE NOT (a1=a2);
A comparison the two EXPLAIN reports shows that the MERGE request would outperform the semantically equivalent UPDATE and INSERT multistatement request.
In the UPDATE and INSERT multistatement request, the following steps are performed in parallel:
Next, there is a JOIN step of the results to OB.t1 with a join condition of ("OB.t1.a1 <> a2").
Example : Target Table Composite Primary Index
The following example shows the necessity of specifying an equality condition on the primary index (and also on the partitioning column set for a row‑partitioned table) in the ON clause for a target table with a composite primary index:
Suppose you create the following two tables:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX (x1, y1);
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX(x2, y2);
The following two MERGE requests are both valid:
MERGE INTO t1
USING t2
ON x1=z2 AND y1=y2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (z2,y2,x2);
MERGE INTO t1
USING t2
ON x1=z2+10 AND y1=y2+20
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT INTO (x1,y1,z1) VALUES (z2+10,y2+20,x2);
The following MERGE request is not valid because the ON clause specifies an equality
condition where x1=z2, but the INSERT specification updates y2 for x1 rather than duplicating the ON clause specification, so it returns an error to the
requestor:
MERGE INTO t1
USING t2
ON x1=z2 AND y1=y2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT INTO (x1,y1,z1) VALUES (y2,z2,x2);
The following MERGE request is not valid because the ON clause specifies an equality
condition where x1=z2+10, but the INSERT specification updates y2+20 for x1 rather than z2+10:
MERGE INTO t1
USING t2
ON x1=z2+10 AND y1=y2+20
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (y2+20, z2+10, x2);
Example : ON Clause Conditions Must Be ANDed With The Primary Index and Partitioning Column Equality Constraints
The following examples show the proper and improper specification of ON clause conditions in a MERGE request.
Consider the following table definitions, with t1 being the target relation and t2 being the source relation for all the examples that follow:
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER)
PRIMARY INDEX (a1);
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER)
PRIMARY INDEX (a2);
The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request, b1=b2:
MERGE INTO t1
USING t2
ON a1=a2 AND b1=b2
WHEN MATCHED THEN
UPDATE SET c1=c2
WHEN NOT MATCHED THEN
INSERT (a2, b2, c2);
The following example is correct because the primary index equality constraint a1=a2 is ANDed with the other specified condition in the request c1+c2=1 OR b1+b2=1.
Even though the second condition is internally disjunctive, the result it evaluates to is ANDed with the primary index condition.
MERGE INTO t1
USING t2
ON a1=a2 AND (c1+c2=1 OR b1+b2=1)
WHEN MATCHED THEN
UPDATE SET c1=c2;
The following example is not valid. It aborts and returns an error message to the
requestor because the primary index equality constraint a1=a2 is ORed with the other specified condition in the request, c1=c2.
The primary index equality constraint, as well as the partitioning column equality constraint if the target table has a partitioned primary index, must always be ANDed to any other conditions you specify in the ON clause.
MERGE INTO t1
USING t2
ON a1=a2 OR b1=b2
WHEN MATCHED THEN
UPDATE SET c1=c2;
Example : For a RPPI Table, the ON Clause Must Specify a Condition on the Partitioning Column and the INSERT Specification Must Match
When the target table of a MERGE operation has a row‑partitioned primary index, the ON clause of the MERGE request must specify a condition on the partitioning column of the table and the order of the columns in the INSERT clause must be the same as the order you specify in the ON clause.
Consider the following table definitions:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX (x1)
PARTITION BY y1;
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX (x2);
You want to use the following MERGE request to insert or update rows in t1, which has a primary index defined on x1 and a partitioning expression defined on y1:
MERGE INTO t1
USING (SELECT *
FROM t2) AS s
ON x1=x2 AND y1=y2
WHEN MATCHED THEN
UPDATE SET z1=z2
WHEN NOT MATCHED THEN
INSERT (x2, y2, z2);
The request is successful because you have defined a condition on y1 in the ON clause (y1=y2) and the specified order of columns in the INSERT clause matches the ordering specified
in the ON clause.
Example : Incorrect Examples Because of ON Clause Errors or Mismatches Between the ON Clause and the INSERT Specification
The following MERGE request fails because its ON clause specifies a condition of x1=z2, but its INSERT clause substitutes y2 for x1:
MERGE INTO t1
USING t2
ON x1=z2 AND y1=y2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (x1, y1, z1) VALUES (y2, z2, x2);
The following MERGE request fails because its ON clause specifies a condition of x1=z2+10, but its INSERT clause inserts y2+20 for x1:
MERGE INTO t1
USING t2
ON x1=z2+10 AND y1=y2+20
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (y2+20, z2+10, x2);
Example : MERGE With ON Clause and UPI or USI
Consider the following table definitions:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX (x1)
PARTITION BY y1;
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX (x2)
UNIQUE INDEX (y2);
When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause specifies an equality condition on the primary index of the target table as a constant, the INSERT specification might or might not match the constant value specified in the ON clause. Either specification is valid, as the following two requests show.
The following MERGE request is valid even though the primary index value specified in the INSERT specification does not match the ON clause primary index specification:
MERGE INTO t1
USING (SELECT *
FROM t2
WHERE y2=10) AS s
ON x1=10
WHEN MATCHED THEN
UPDATE SET z1=z2
WHEN NOT MATCHED THEN
INSERT (x2, y2, z2);
The following MERGE request is valid because the primary index value specified in the INSERT specification matches the ON clause primary index specification:
MERGE INTO t1
USING (SELECT x2, y2, z2
FROM t2
WHERE y2=10) AS s
ON x1=10 AND y1=z2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (10, z2, x2);
When the source relation is guaranteed to be a single row, either because you specify a value list or because it is created from a single table subquery with a UPI or USI constraint, and the ON clause has an equality condition on the primary index of the target table that is not a constant, the INSERT clause primary index specification must match the primary index value for the target table specified in the ON clause. The UPDATE specification also must not update the primary index of the target table.
The following MERGE request is valid because the primary index value specified in the INSERT specification, y2, matches the primary index value specified in the ON clause, and the UPDATE specification does not update the primary index of t1:
MERGE INTO t1
USING (SELECT x2, y2, z2
FROM t2
WHERE y2=10) AS s
ON x1=y2 AND y1=z2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (y2, z2, x2);
The following MERGE request fails because the source relation is not guaranteed to be a single row. The failure to guarantee a single row occurs because z2 is neither a UPI nor a USI, so the INSERT specification must match the ON clause specification, which it does not.
MERGE INTO t1
USING (SELECT x2, y2, z2
FROM t2
WHERE z2=10) AS s
ON x1=10 AND y1=20
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (y2, z2, x2);
Example : MERGE Updating a Primary Index
You cannot update primary index or partitioning column unless the source is a valid single‑row subquery.
Suppose you have created the following tables:
CREATE TABLE t1 (
x1 INTEGER,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX(x1);
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX(x2)
UNIQUE INDEX(y2);
The following case is valid:
MERGE INTO t1
USING (SELECT x2, y2, z2
FROM t2
WHERE y2=10) AS s
ON x1=10 AND y1=20
WHEN MATCHED THEN
UPDATE SET x1=10
WHEN NOT MATCHED THEN
INSERT (y2, z2, x2);
The following case is not valid because while the subquery guarantees a single row, no constant in specified in the ON clause equality condition, so you cannot update the primary index as this request attempts to do:
MERGE INTO t1
USING (SELECT x2,y2, z2
FROM t2
WHERE y2=10) AS s
ON x1=y2 AND y1=z2
WHEN MATCHED THEN
UPDATE SET x1=10
WHEN NOT MATCHED THEN
INSERT (y2, z2, x2);
Example : MERGE and Identity Columns
The primary index of the target table cannot be an identity column if you specify an INSERT clause even when the ON and INSERT clauses are valid.
However, if the source relation is created from a single row subquery, or if you do not specify an INSERT clause, then the target table primary index can also be an identity column.
Consider the following rules and examples based on these table definitions:
CREATE TABLE t1 (
x1 INTEGER GENERATED ALWAYS AS IDENTITY,
y1 INTEGER,
z1 INTEGER)
PRIMARY INDEX(x1);
CREATE TABLE t2 (
x2 INTEGER,
y2 INTEGER,
z2 INTEGER)
PRIMARY INDEX(x2)
UNIQUE INDEX(y2);
The rules, explicitly stated, are as follows:
For example, the following MERGE request is valid because no WHEN NOT MATCHED THEN INSERT clause is specified:
MERGE INTO t1
USING (SELECT x2, y2, z2
FROM t2
WHERE y2 = 1)
ON x1 = x2
WHEN MATCHED THEN
UPDATE SET y1 = y2;
For example, the following MERGE request fails because it specifies a WHEN NOT MATCHED THEN INSERT clause that attempts to insert the value of x2 into x1, which is both the primary index of t1 and an identity column:
MERGE INTO t1
USING (SELECT x2, y2, z2
FROM t2
WHERE y2 = 1)
ON x1 = x2
WHEN MATCHED THEN
UPDATE SET y1 = y2
WHEN NOT MATCHED THEN
INSERT (x2, y2, z2);
Example : MERGE and Target Columns
The following example fails because the INSERT specification of the WHEN NOT MATCHED clause specifies a column, z1, from the target table t1, which is an illegal operation.
MERGE INTO t1
USING (SELECT x2,y2, z3
FROM t2, t3
WHERE y2=10) AS s
ON x1=y2 AND t4.x4=z2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (x1,y1, z1) VALUES (y2, t1.z1, x2);
Example : Failures Caused By Referencing Tables Other Than the Source or Target in the ON, WHEN MATCHED, or WHEN NOT MATCHED Clauses
The following examples fail because they reference a table other than the source or target table in either their ON, WHEN MATCHED, or WHEN NOT MATCHED clauses.
The following example fails because table t4 is neither the derived source table s nor the target table t1.
MERGE INTO t1
USING (SELECT x2,y2, z3
FROM t2, t3
WHERE y2=10) AS s
ON x1=y2 AND t4.x4=z2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
SELECT (y2, z2, x2);
The following example fails because table t3 is neither the derived source table s nor the target table t1. Even though t3 is specified in the USING source table subquery, it violates the restriction that only source and target tables can be referenced in an ON clause.
MERGE INTO t1
USING (SELECT x2,y2, z3
FROM t2, t3
WHERE y2=10) AS s
ON x1=y2 AND t3.x4=z2
WHEN MATCHED THEN
UPDATE SET z1=10
WHEN NOT MATCHED THEN
INSERT (y2, z2, x2);
Example : Specifying the Partitioning Column Set in the ON Clause When the Target Relation Has a Row-Partitioned Primary Index
Consider the following table definitions where t1 has a primary index on a1 and is partitioned on column b1.
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER)
PRIMARY INDEX (a1)
PARTITION BY b1;
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER)
PRIMARY INDEX (a2);
The following MERGE request is valid because it specifies the partitioning column, b1, of the target table, t1, in its ON clause:
MERGE INTO t1
USING t2
ON a1=a2 AND b1=b2
WHEN MATCHED THEN
UPDATE SET c1=c2
WHEN NOT MATCHED THEN
INSERT (a2, b2, c2);
The following MERGE request aborts and returns an error message to the requestor because it does not specify the partitioning column of the target table, b1, in its ON clause:
MERGE INTO t1
USING t2
ON a1=a2
WHEN MATCHED THEN
UPDATE SET c1=c2
WHEN NOT MATCHED THEN
INSERT (a2, b2, c2);
The following MERGE request aborts and returns an error message to the requestor because its INSERT specification orders columns b2 and c2 in a different sequence than they were specified in its ON clause. The INSERT specification must always match the ON clause constraints on the primary index of the target table, and its partitioning column set if the target table has row-partitioning.
MERGE INTO t1
USING t2
ON a1=a2 AND b1=b2
WHEN MATCHED THEN
UPDATE SET c1=c2
WHEN NOT MATCHED THEN
INSERT (a2, c2, b2);
If the target table has row-partitioning, the values of the partitioning columns must also be specified in search_condition, and the INSERT clause must specify the same partitioning column values as search_condition.
Example : You Cannot Substitute the System‑Derived PARTITION Column For the Partitioning Column Set For a MERGE Operation With a RPPI Target Table
Assume you have defined the following source and target relation definitions:
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER)
PRIMARY INDEX (a1)
PARTITION BY b1;
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER)
PRIMARY INDEX (a2),
UNIQUE INDEX(b2);
The following example fails because you cannot substitute the system‑derived PARTITION column for the partitioning column set of the target relation in the ON clause.
MERGE INTO t1
USING t2
ON a1=a2 AND t1.PARTITION=10
WHEN MATCHED THEN
UPDATE SET b1=10
WHEN NOT MATCHED THEN
INSERT (a2, b2, c2);
This MERGE request is valid because it specifies conditions on the primary index of the target table, a1, and its partitioning column, b1:
MERGE INTO t1
USING t2
ON a1=a2 AND b1=10
WHEN MATCHED THEN
UPDATE SET c1=c2;
The following MERGE request, again written against the same set of source and target tables, is not valid because it fails to specify the partitioning column for the target table, b1, in its ON clause.
MERGE INTO t1
USING t2
ON a1=a2
WHEN MATCHED THEN
UPDATE SET c1=c2;
You can specify a system‑derived PARTITION column‑based condition in the ON clause, but only
as a residual condition. For example, the following example works correctly because
the primary index equality condition a1=a2 and the target table partitioning column condition b1=10 are both specified. The system treats the additional t1.PARTITION condition, t1.PARTITION=25 as a residual condition only.
Example : With a Guaranteed Single‑Row Source Relation, You Do Not Need To Specify the Partitioning Column Set
Because the following MERGE request guarantees a single‑row source relation by specifying an equality condition on the USI of target RPPI table t1, it is not necessary to specify the partitioning column set:
CREATE TABLE t1 (
a1 INTEGER,
b1 INTEGER,
c1 INTEGER)
PRIMARY INDEX (a1)
PARTITION BY b1;
CREATE TABLE t2 (
a2 INTEGER,
b2 INTEGER,
c2 INTEGER)
PRIMARY INDEX (a2),
UNIQUE INDEX(b2);
MERGE INTO t1
USING (SELECT *
FROM t2
WHERE b2=10) AS s
ON a1=1
WHEN MATCHED THEN
UPDATE SET c1=c2
WHEN NOT MATCHED THEN
INSERT (c2, b2, c2);
Example : Using the Target Table as the Source Table
The following example uses target table t1 as its source table:
MERGE INTO t1
USING t1 AS s
ON t1.a1 = s.a1
WHEN MATCHED THEN
UPDATE SET b1 = 10
WHEN NOT MATCHED THEN
INSERT VALUES (s.a1, s.b1, s.c1);
Example : Using the BEGIN Period Bound Function as a Condition When Merging Into a RPPI Table
Assume you define the following two tables.
CREATE SET TABLE testing.t11 (
a INTEGER,
b DATE FORMAT 'YY/MM/DD',
c DATE FORMAT 'YY/MM/DD')
PRIMARY INDEX (a);
CREATE SET TABLE testing.t12 (
a INTEGER,
b PERIOD(DATE),
c INTEGER)
PRIMARY INDEX (a)
PARTITION BY RANGE_N((BEGIN(b) BETWEEN DATE '2009-01-01'
AND DATE '2011-12-31'
EACH INTERVAL '1' MONTH);
The following MERGE request merges rows from t11 in to t12.
MERGE INTO t12
USING t11
ON t12.a = t11.a
AND BEGIN (t12.b) = t11.b
WHEN MATCHED THEN
UPDATE SET c= 4
WHEN NOT MATCHED THEN
INSERT VALUES (t11.a, PERIOD(t11.b), 4);
Example : Using the BEGIN and END Period Bound Functions as Conditions When Merging Into a RPPI Table
Assume you define the following two tables:
CREATE SET TABLE testing.t21 (
a INTEGER,
b DATE FORMAT 'YY/MM/DD',
c DATE FORMAT 'YY/MM/DD')
PRIMARY INDEX (a);
CREATE SET TABLE testing.t22 (
a INTEGER,
b PERIOD (DATE))
PRIMARY INDEX (a)
PARTITION BY (CASE_N((END(b))<= DATE '2008-03-31',
(END(b))<= DATE '2008-06-30',
(END(b))<= DATE '2008-09-30',
(END(b))<= DATE '2008-12-31'),
CASE_N((BEGIN(b))> DATE '2008-12-31')
(BEGIN(b))> DATE '2008-09-30',
(BEGIN(b))> DATE '2008-06-30',
(BEGIN(b))> DATE '2008-03-31',);
The following MERGE request merges rows from t11 in to t22.
MERGE INTO t22
USING t21
ON t22.a = t21.a
AND BEGIN(t22.b) = t21.b
AND END(t22.b) = t21.c
WHEN MATCHED THEN
UPDATE SET c=4
WHEN NOT MATCHED THEN
INSERT VALUES (t21.a, PERIOD(t21.b, t21.c), 4);
Example : Failure Because the Matching Condition is Defined on a PERIOD Bound Function
This example fails because the matching condition of the MERGE request is defined on a BEGIN Period bound function.
CREATE TABLE source(
a INTEGER,
b PERIOD(DATE),
c INTEGER);
CREATE TABLE target(
i INTEGER,
j PERIOD(DATE),
k INTEGER)
PRIMARY INDEX(i)
PARTITION BY Begin(j);
INSERT INTO source(1, PERIOD(DATE, UNTIL_CHANGED), 1);
MERGE INTO target
USING source
ON a=i AND BEGIN(j) = END(b)
WHEN MATCHED THEN
UPDATE SET k=c ;
Example : Invoking an SQL UDF From a MERGE Request
This example shows how to invoke an SQL UDF named value_expression at several points within a MERGE request.
MERGE INTO t1
USING (SELECT a2, b2, c2
FROM t2
WHERE test.value_expression(b2, c2))
source_tbl(a2, b2, c2)
ON a1 = source_tbl.a2 AND
b1 = test.value_expression(source_tbl.b2, source_tbl.c2)
WHEN MATCHED THEN
UPDATE SET b1 = b2, c1 = test.value_expression(2,3)
WHEN NOT MATCHED THEN
INSERT (a2, test.value_expression(4,5), c2);
Example : MERGE with Isolated Loading
MERGE WITH CONCURRENT ISOLATED LOADING INTO payrolldb.employee E USING( SELECT * FROM wt_employee ) dt
ON e.lastname = dt.lastname
AND e.firstname= dt.firstname
WHEN MATCHED THEN
UPDATE SET departmentid = dt. departmentid
WHEN NOT MATCHED THEN
INSERT VALUES(dt.employeeid, dt.firstname, dt.lastname, dt.dateofbirth, dt.dateofhire, dt.departmentid);
Example : Executing a MERGE Update Request When Both the Target Table and the Source Table Have Row‑Level Security Constraints
Assume that:
The update of table_1 is valid and Teradata Database takes the constraint values for target table table_1, which are not specified in the request, from the constraint values defined for source table table_2.
MERGE INTO table_1 AS target
USING table_2 AS source
ON (target.col_1 = source.col_1)
WHEN MATCHED THEN
UPDATE SET level = source.col_2;