The
MERGE statement updates a target (a table or view, or the underlying
tables or views of a fullselect) using data from a source (result
of a table reference).
Rows in the target that match the source can be deleted
or updated as specified, and rows that do not exist in the target
can be inserted. Updating, deleting or inserting a row in a view updates,
deletes or inserts the row in the tables on which the view is based.
Invocation
This statement can be embedded
in an application program or issued through the use of dynamic SQL
statements. It is an executable statement that can be dynamically
prepared.
Authorization
The privileges held by the
authorization ID of the statement must include at least one of the
following authorities:
- If an insert operation is specified, INSERT privilege on the table
or view; if a delete operation is specified, DELETE privilege on the
table or view; and if an update operation is specified, either:
- UPDATE privilege on the table or view
- UPDATE privilege on each column that is to be updated
- CONTROL privilege on the table
- DATAACCESS authority
The privileges held by the authorization ID of the statement
must also include at least one of the following authorities:
- SELECT privilege on every table or view identified in the table-reference
- CONTROL privilege on the tables or views identified in the table-reference
- DATAACCESS authority
If
search-condition,
insert-operation, or
assignment-clause includes a subquery, the privileges held by the authorization
ID of the statement must also include at least one of the following
authorities:
- SELECT privilege on every table or view identified in the subquery
- CONTROL privilege on the tables or views identified in the subquery
- DATAACCESS authority
If
a
row-fullselect is included in the assignment,
the privileges held by the authorization ID of the statement must
include at least one of the following authorities for each referenced
table, view, or nickname:
- SELECT privilege
- CONTROL privilege
- DATAACCESS authority
If an expression that refers to a function is specified,
the privilege set must include any authority that is necessary to
execute the function.
Syntax
>>-MERGE INTO--+-table-name-------+----------------------------->
+-view-name--------+
'-(--fullselect--)-'
>--+------------------------+--USING--table-reference----------->
'-| correlation-clause |-'
>--ON--search-condition----------------------------------------->
.--------------------------------------------------------------------.
V |
>----WHEN--| matching-condition |--THEN--+-| modification-operation |-+-+-->
'-signal-statement-----------'
.-ELSE IGNORE-.
>--+-------------+--+--------------+---------------------------><
'-WITH--+-RR-+-'
+-RS-+
+-CS-+
'-UR-'
correlation-clause
.-AS-.
|--+----+--correlation-name--+-----------------------+----------|
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
matching-condition
|--+-----+--MATCHED--+-----------------------+------------------|
'-NOT-' '-AND--search-condition-'
modification-operation
|--+-| update-operation |-+-------------------------------------|
+-| delete-operation |-+
'-| insert-operation |-'
update-operation
|--UPDATE--+---------------+--SET--| assignment-clause |--------|
'-period-clause-'
assignment-clause
.-,----------------------------------------------------------------.
V |
|----+-column-name-- = --+-expression-+-----------------------------+-+--|
| +-DEFAULT----+ |
| '-NULL-------' |
| .-,-----------. .-,------------------. |
| V | V (1) | |
'-(----column-name-+--)-- = --(--+---+-expression-----+-+-+--)-'
| +-DEFAULT--------+ |
| '-NULL-----------' |
| (2) |
'-row-fullselect---------'
delete-operation
|--DELETE--+---------------+------------------------------------|
'-period-clause-'
insert-operation
|--INSERT--+-----------------------+---------------------------->
| .-,-----------. |
| V | |
'-(----column-name-+--)-'
>--VALUES--+-+-expression-+-----------+-------------------------|
| +-DEFAULT----+ |
| '-NULL-------' |
| .-,--------------. |
| V | |
'-(----+-expression-+-+--)-'
+-DEFAULT----+
'-NULL-------'
period-clause
|--FOR PORTION OF BUSINESS_TIME--FROM--value1--TO--value2-------|
Notes:
- The number of expressions, NULLs, and DEFAULTs must match
the number of column names.
- The number of columns in the select list must match the number
of column names.
Description
- table-name, view-name, or (fullselect)
- Identifies the target of the update, delete, or insert operations
of the merge. The name must identify a table or view that exists at
the current server, but it must not identify a catalog table, a system-maintained
materialized query table, a view of a catalog table, a read-only view,
or a view that directly or indirectly contains a WHERE clause that
references a subquery or a routine defined with NOT DETERMINISTIC
or EXTERNAL ACTION (SQLSTATE 42807).
If the target of the merge
operation is a fullselect, the fullselect must be updatable, deletable,
or insertable as defined in the "Updatable views", "Deletable
views", or "Insertable views" Notes items in the description of the CREATE
VIEW statement.
You cannot use a period-clause in an update-operation or a delete-operation if the target
of the merge operation is a union-all view or a fullselect.
You cannot use a nickname (a reference to a remote, federated table)
as the target table.
- correlation-clause
- Can be used within search-condition or
on the right side of an assignment-clause to designate a table, view, or fullselect. For a description of correlation-clause, see "table-reference" in the description of "Subselect".
- USING table-reference
- Specifies a set of rows as a result table to be merged into the
target. If the result table is empty, a warning is returned (SQLSTATE
02000).
- ON search-condition
- Specifies which rows from table-reference are to be used in the update and delete operation of the merge,
and which rows are to be used in the insert operation of the merge.
The search-condition is applied to each
row of the target table and result table of the table-reference. For those rows of the result table of the table-reference where the result of the search-condition is true, the specified update or delete operation is performed.
For those rows of the result table of the table-reference where the result of the search-condition is not true, the specified insert operation is performed.
The
search-condition has the following restrictions
(SQLSTATE 42972 unless otherwise noted):
- It cannot contain any subqueries, scalar or otherwise
- It cannot include any dereference operations or the DEREF function
where the reference value is other than the object identifier column
- It cannot include an SQL function
- It cannot include an XMLQUERY or XMLEXISTS expression
- Any column that is referenced in an expression of the search-condition must be a column of the target table,
view, or table-reference
- Any function that is referenced in an expression of the join-condition of a full outer join must be deterministic
and have no external action
- It cannot be include an aggregate function (SQLSTATE 42903)
If the search-condition is false
or unknown for every row in table-reference, a warning is returned (SQLSTATE 02000).
- WHEN matching-condition
- Specifies the condition under which the modification-operation or the signal-statement is executed.
Each matching-condition is evaluated in
order of specification. Rows for which the matching-condition evaluates to true are not considered in subsequent matching
conditions.
- MATCHED
- Indicates the operation to be performed on the rows where the
ON search condition is true. Only UPDATE, DELETE, or signal-statement can be specified after THEN.
- AND search-condition
- Specifies a further search condition to be applied against the
rows that matched the ON search condition for the operation to be
performed after THEN.
- NOT MATCHED
- Indicates the operation to be performed on the rows where the
ON search condition is false or unknown. Only INSERT or signal-statement can be specified after THEN.
- AND search-condition
- Specifies a further search condition to be applied against the
rows that did not match the ON search condition for the operation
to be performed after THEN.
- THEN modification-operation
- Specifies the operation to execute when the matching-condition evaluates to true.
- update-operation
- Specifies the update operation to be executed for the rows where
the matching-condition evaluates to true.
- UPDATE
- Introduces the update operation.
- period-clause
- Specifies that a period clause is applied to the update operation
in the MERGE statement. For more information about the effects of
a period clause specified in the context of an update operation, see
the UPDATE statement topic.
- SET
- Introduces the assignment of values to column names.
- assignment-clause
- Specifies a list of column updates.
- column-name
- Identifies a column to be updated. The column-name must identify a column of the specified table or view, but
not a view column derived from a scalar function, constant, or expression.
A column must not be specified more than once (SQLSTATE 42701).
A view column derived from the same column as another column of the
view can be updated, but both columns cannot be updated in the same
MERGE statement (SQLSTATE 42701).
- expression
- Indicates
the new value of the column. The expression must not include an aggregate function except when it occurs within
a scalar fullselect (SQLSTATE 42903).
An expression can contain references to columns of the table-name or view-name. For each row that
is updated, the value of such a column in an expression is the value
of the column in the row before the row is updated.
If expression is a reference to a single column of
the source table, the source table column value may have been specified
with an extended indicator variable value. The effects of such indicator
variables apply to the corresponding target columns of the assignment-clause.
If expression is a single host variable, or a host
variable being explicitly cast, the host variable can include an indicator
variable that is enabled for extended indicator variables.
When extended
indicator variables are enabled, the extended indicator variable values
of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539)
if either of the following statements is true:
- The expression is more complex than a single host variable with
explicit casts
- The target column has data type of structured type
- DEFAULT
- The default value assigned to the column. DEFAULT can be specified
only for columns that have a default value. For information about
default values of data types, see the description of the DEFAULT clause
in the "CREATE TABLE" statement.
DEFAULT must be specified
for a column that was defined as GENERATED ALWAYS. A valid value can
be specified for a column that was defined as GENERATED BY DEFAULT.
- NULL
- Specifies the null value as the new value of the column. Specify
NULL only for nullable columns (SQLSTATE 23502).
- row-fullselect
- Specifies a fullselect that returns a single row. The result column
values are assigned to each corresponding column-name. If the fullselect returns no rows, the null value is assigned
to each column; an error occurs if any column to be updated is not
nullable. An error also occurs if there is more than one row in the
result.
A row-fullselect can contain references
to columns of the target table of the MERGE statement. For each row
that is updated, the value of such a column in an expression is the
value of the column in the row before the row is updated. An error
is returned if there is more than one row in the result (SQLSTATE
21000).
- delete-operation
- Specifies the delete operation to be executed for the rows where
the matching-condition evaluates to true.
- DELETE
- Introduces the delete operation.
- period-clause
- Specifies that a period clause is applied to the delete operation
in the MERGE statement. For more information about the effects of
a period clause specified in the context of a delete operation, see
the DELETE statement topic.
- insert-operation
- Specifies the insert operation to be executed for the rows where
the matching-condition evaluates to true.
- INSERT
- Introduces a list of column names and row value expressions to
be used for the insert operation.
The number of values for the
row in the row value expression must equal the number of names in
the insert column list. The first value is inserted in the first column
in the list, the second value in the second column, and so on.
- (column-name,...)
- Specifies the columns for which the insert values are provided.
Each name must identify a column of the table or view. The same column
must not be identified more than once (SQLSTATE 42701). A view column
that cannot accept insert values must not be identified. A value cannot
be inserted into a view column that is derived from:
- A constant, expression, or scalar function
- The same base table column as some other column of the view
If the object of the operation is a view with such columns,
a list of column names must be specified, and the list must not identify
these columns.
Omission of the column list is an implicit
specification of a list in which every column of the table (that
is not defined as implicitly hidden) or view is identified in
left-to-right order. This list is established when the statement is
prepared, and therefore does not include columns that were added to
a table after the statement was prepared.
- VALUES
- Introduces one or more rows of values to be inserted.
- expression
- Any expression that does not include a column name (SQLSTATE 42703).
If expression is a reference to a single column of
the source table, the source table column value may have been specified
with an extended indicator variable value. The effects of such indicator
variables apply to the corresponding target columns of the insert-operation.
If expression is a single host variable, or a host
variable being explicitly cast, the host variable can include an indicator
variable (or in the case of a host structure, an indicator array)
that is enabled for extended indicator variables.
When extended indicator
variables are enabled, the extended indicator variable values of default
(-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either
of the following statements is true:
- The expression is more complex than a single host variable with
explicit casts
- The target column has data type of structured type
- DEFAULT
- The default value assigned to the column. DEFAULT can be specified
only for columns that have a default value. For information about
default values of data types, see the description of the DEFAULT clause
in the "CREATE TABLE" statement.
DEFAULT must be specified
for a column that was defined as GENERATED ALWAYS. A valid value can
be specified for a column that was defined as GENERATED BY DEFAULT.
- NULL
- Specifies the null value as the value of the column. Specify NULL
only for nullable columns (SQLSTATE 23502).
- signal-statement
- Specifies the SIGNAL statement that is to be executed to return
an error when the matching-condition evaluates
to true.
- ELSE IGNORE
- Specifies that no action is to be taken for the rows where no matching-condition evaluates to true. If all rows
of table-reference are ignored, a warning
is returned (SQLSTATE 02000).
- WITH
- Specifies the isolation level at which the MERGE statement is
executed.
- RR
- Repeatable Read
- RS
- Read Stability
- CS
- Cursor Stability
- UR
- Uncommitted Read
The default isolation level of the statement is
the isolation level of the package in which the statement is bound.
Rules
- More than one modification-operation (UPDATE SET, DELETE, or insert-operation), or signal-statement can be specified
in a single MERGE statement.
- Each row in the target can only be operated on once. A row in
the target can only be identified as MATCHED with one row in the result
table of the table-reference (SQLSTATE 21506).
A nested SQL operation (RI or trigger except INSTEAD OF trigger) cannot
specify the target table (or a table within the same table hierarchy)
as a target of an UPDATE, DELETE, INSERT, or MERGE statement (SQLSTATE
27000).
- Security policy: If the identified target table
or the base table of the identified target view is protected with
a security policy, the session authorization ID must have the label-based
access control (LBAC) credentials that allow the following types of
access.
- For the update operation:
- Write access to all protected columns that are being updated (SQLSTATE
42512)
- Write access for any explicit value provided for a DB2SECURITYLABEL
column for security policies that were created with the RESTRICT NOT
AUTHORIZED WRITE SECURITY LABEL option (SQLSTATE 23523)
- Read and write access to all rows that are being updated (SQLSTATE
42519)
The session authorization ID must also have been granted a
security label for write access for the security policy if an implicit
value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which
can happen when:
- The DB2SECURITYLABEL column is not included in the list of columns
that are to be updated (and so it will be implicitly updated to the
security label for write access of the session authorization ID)
- A value for the DB2SECURITYLABEL column is explicitly provided
but the session authorization ID does not have write access for
that value, and the security policy is created with the OVERRIDE
NOT AUTHORIZED WRITE SECURITY LABEL option
- For the delete operation:
- Write access to all protected columns (SQLSTATE 42512)
- Read and write access to all of the rows that are selected for
deletion (SQLSTATE 42519)
- For the insert operation:
- Write access to all protected columns for which a data value is
explicitly provided (SQLSTATE 42512)
- Write access for any explicit value provided for a DB2SECURITYLABEL
column for security policies that were created with the RESTRICT NOT
AUTHORIZED WRITE SECURITY LABEL option (SQLSTATE 23523)
The session authorization ID must also have been granted a
security label for write access for the security policy if an implicit
value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which
can happen when:
- A value for the DB2SECURITYLABEL column is not explicitly provided
- A value for the DB2SECURITYLABEL column is explicitly provided
but the session authorization ID does not have write access for
that value, and the security policy is created with the OVERRIDE
NOT AUTHORIZED WRITE SECURITY LABEL option
- INSTEAD OF triggers: If a view is specified as the
target of the MERGE statement, either no INSTEAD OF triggers should
be defined for the view, or an INSTEAD OF trigger should be defined
for each of the update, delete, and insert operations (SQLSTATE 428FZ).
- Extended indicator variable usage: If enabled, negative indicator
variable values outside the range of -1 through -7 must not be input
(SQLSTATE 22010). Also, if enabled, the default and unassigned extended
indicator variable values must not appear in contexts in which they
are not supported (SQLSTATE 22539).
- Extended indicator variables in the assignment-clause:
An expression that is a reference to a single
column of the source table, a single host variable, or a host variable
being explicitly cast can result in assigning an extended indicator
variable-based value. Assigning the extended indicator variable-based
value of unassigned has the effect of leaving the target column set
to its current value, as if it had not been specified in the statement.
Assigning the extended indicator variable-based value of default
assigns the default value of the column. For information on default
values of data types, see the description of the DEFAULT clause in the "CREATE TABLE" statement.
If
a target column is not updatable (for example, a column in a view
that is defined as an expression), then it must be assigned the extended
indicator variable-based value of unassigned (SQLSTATE 42808).
If the target column is a column defined as GENERATED ALWAYS,
then it must be assigned the DEFAULT keyword, or the extended indicator
variable-based values of default or unassigned (SQLSTATE 428C9).
The assignment-clause must not assign
all target columns to an extended indicator variable-based value of
unassigned (SQLSTATE 22540).
- Extended indicator variables in the insert-operation: An expression that is a reference
to a single column of the source table, a single host variable, or
a host variable being explicitly cast can result in inserting an extended
indicator variable-based value. In insert-operation, a value of unassigned has the effect of setting the column
to its default value.
If a target column is not updatable, then
it must be assigned the extended indicator variable-based value of
unassigned (SQLSTATE 42808), unless it is a column defined as GENERATED
ALWAYS. If the target column is a column defined as GENERATED ALWAYS,
then it must be assigned the DEFAULT keyword, or the extended indicator
variable-based values of default or unassigned (SQLSTATE 428C9).
For other rules that affect the update, insert, or delete
operation portion of the MERGE statement, see the "Rules" section
of the corresponding statement description.
Notes
- Order of processing:
- Determine the set of rows to be processed from the source and
target. If CURRENT TIMESTAMP is used in this statement, only one clock
reading is done for the whole statement.
- Use the ON clause to classify these rows as either MATCHED or
NOT MATCHED.
- Evaluate any matching-condition in the
WHEN clauses.
- Evaluate any expression in any assignment-clause and insert-operation.
- Execute each signal-statement.
- Apply each modification-operation to
the applicable rows in the order of specification. The constraints
and triggers activated by each modification-operation are executed for the modification-operation. Statement-level triggers are activated even if no rows
satisfy the modification-operation. Each modification-operation can affect the triggers
and referential constraints of each subsequent modification-operation.
- Statement level atomicity: If an error occurs during
execution of the MERGE statement, the whole statement is rolled back.
- Number of rows updated: When a MERGE statement completes
execution, the value of the ROW_COUNT item for GET DIAGNOSTICS and
SQLERRD(3) in the SQLCA is the number of rows operated on by the MERGE
statement, excluding rows identified by the ELSE IGNORE clause. The
value in SQLERRD(3) does not include the number of rows that were
operated on as a result of constraints or triggers. The value in SQLERRD(5)
includes the number of these rows.
- Inserted row cannot also be updated: No attempt
is made to update a row in the target that did not already exist before
the MERGE statement was executed; that is, there are no updates of
rows that were inserted by the MERGE statement.
- Extended indicator variables and update triggers: If a target
column has been assigned with an extended indicator variable-based
value of unassigned, that column is not considered to have been updated.
That column is treated as if it had not been specified in the OF column-name list of any update trigger defined
on the target table.
- Extended indicator variables and insert triggers: No change in
the activation of insert triggers results from the use of extended
indicator variables. If all columns in the implicit or explicit column
list have been assigned to an extended indicator variable-based value
of unassigned or default, an insert where all columns have their respective
default values is attempted. If the insert is successful, the insert
trigger is activated.
- Extended indicator variables and deferred error checks: When
extended indicator variables are enabled, validation that would otherwise
be done in statement preparation to recognize an insert into, or update
of, a non-updatable column, is deferred until statement execution.
Whether an error should be reported can be determined only during
execution.
- Considerations for system-period temporal tables: When MERGE
is processed for a system-period temporal table, the rows are impacted
in the same way as if the specific data change operations had been
invoked. See UPDATE statement, DELETE statement, and INSERT statement
topics for more information.
- Considerations for application-period temporal tables and triggers; When a row is deleted and the FOR PORTION OF BUSINESS_TIME clause
is specified, additional rows may be implicitly inserted to reflect
any portion of the row that was not deleted. Any existing delete triggers
are activated for the rows deleted, and any existing insert triggers
are activated for rows that are implicitly inserted. When a row is
updated and the FOR PORTION OF BUSINESS_TIME clause is specified,
additional rows may be implicitly inserted to reflect any portion
of the row that was not updated. Any existing update triggers are
activated for the rows updated, and any existing insert triggers are
activated for rows that are implicitly inserted.
- Considerations for a MERGE without a column list in the insert-operation: A MERGE statement without a column list specified as part of the
insert-operation does not include implicitly hidden columns. Columns
that are defined as implicitly hidden and not null must have a defined
default value.
Examples
- Example 1: For activities whose description has been changed,
update the description in the archive table. For new activities, insert
into the archive table. The archive and activities table both have
activity as a primary key.
MERGE INTO archive ar
USING (SELECT activity, description FROM activities) ac
ON (ar.activity = ac.activity)
WHEN MATCHED THEN
UPDATE SET
description = ac.description
WHEN NOT MATCHED THEN
INSERT
(activity, description)
VALUES (ac.activity, ac.description)
- Example 2: Using the shipment table, merge rows into the
inventory table, increasing the quantity by part count in the shipment
table for rows that match; else insert the new partno into the inventory table.
MERGE INTO inventory AS in
USING (SELECT partno, description, count FROM shipment
WHERE shipment.partno IS NOT NULL) AS sh
ON (in.partno = sh.partno)
WHEN MATCHED THEN
UPDATE SET
description = sh.description,
quantity = in.quantity + sh.count
WHEN NOT MATCHED THEN
INSERT
(partno, description, quantity)
VALUES (sh.partno, sh.description, sh.count)
- Example 3: Using the transaction table, merge rows into
the account table, updating the balance from the set of transactions
against an account ID and inserting new accounts from the consolidated
transactions where they do not already exist.
MERGE INTO account AS a
USING (SELECT id, sum(amount) sum_amount FROM transaction
GROUP BY id) AS t
ON a.id = t.id
WHEN MATCHED THEN
UPDATE SET
balance = a.balance + t.sum_amount
WHEN NOT MATCHED THEN
INSERT
(id, balance)
VALUES (t.id, t.sum_amount)
- Example 4: Using the transaction_log table, merge rows
into the employee_file table, updating the phone and office with the
latest transaction_log row based on the transaction time, and inserting
the latest new employee_file row where the row does not already exist.
MERGE INTO employee_file AS e
USING (SELECT empid, phone, office
FROM (SELECT empid, phone, office,
ROW_NUMBER() OVER (PARTITION BY empid
ORDER BY transaction_time DESC) rn
FROM transaction_log) AS nt
WHERE rn = 1) AS t
ON e.empid = t.empid
WHEN MATCHED THEN
UPDATE SET
(phone, office) =
(t.phone, t.office)
WHEN NOT MATCHED THEN
INSERT
(empid, phone, office)
VALUES (t.empid, t.phone, t.office)
- Example 5: Using dynamically supplied values for an employee
row, update the master employee table if the data corresponds to an
existing employee, or insert the row if the data is for a new employee.
The following example is a fragment of code from a C program.
hv1 =
"MERGE INTO employee AS t
USING TABLE(VALUES(CAST (? AS CHAR(6)), CAST (? AS VARCHAR(12)),
CAST (? AS CHAR(1)), CAST (? AS VARCHAR(15)),
CAST (? AS SMALLINT), CAST (? AS INTEGER)))
s(empno, firstnme, midinit, lastname, edlevel, salary)
ON t.empno = s.empno
WHEN MATCHED THEN
UPDATE SET
salary = s.salary
WHEN NOT MATCHED THEN
INSERT
(empno, firstnme, midinit, lastname, edlevel, salary)
VALUES (s.empno, s.firstnme, s.midinit, s.lastname, s.edlevel,
s.salary)";
EXEC SQL PREPARE s1 FROM :hv1;
EXEC SQL EXECUTE s1 USING '000420', 'SERGE', 'K', 'FIELDING', 18, 39580;
- Example 6: Update the list of activities organised by Group
A in the archive table. Delete all outdated activities and update
the activities information (description and date) in the archive table
if they have been changed. For new upcoming activities, insert into
the archive. Signal an error if the date of the activity is not known.
The date of the activities in the archive table must be specified.
Each group has an activities table. For example, activities_groupA
contains all activities that they organize, and the archive table
contains all upcoming activities organized by different groups in
a company. The archive table has (group, activity) as the primary
key, and date is not nullable. All activities tables have activity
as the primary key. The last_modified column in the archive is defined
with CURRENT TIMESTAMP as the default value.
MERGE INTO archive ar
USING (SELECT activity, description, date, last_modified
FROM activities_groupA) ac
ON (ar.activity = ac.activity) AND ar.group = 'A'
WHEN MATCHED AND ac.date IS NULL THEN
SIGNAL SQLSTATE '70001'
SET MESSAGE_TEXT =
ac.activity CONCAT ' cannot be modified. Reason: Date is not known'
WHEN MATCHED AND ac.date < CURRENT DATE THEN
DELETE
WHEN MATCHED AND ar.last_modified < ac.last_modified THEN
UPDATE SET
(description, date, last_modified) = (ac.description, ac.date, DEFAULT)
WHEN NOT MATCHED AND ac.date IS NULL THEN
SIGNAL SQLSTATE '70002'
SET MESSAGE_TEXT =
ac.activity CONCAT ' cannot be inserted. Reason: Date is not known'
WHEN NOT MATCHED AND ac.date >= CURRENT DATE THEN
INSERT
(group, activity, description, date)
VALUES ('A', ac.activity, ac.description, ac.date)
ELSE IGNORE