MERGE Syntax | SQL Statements | Teradata Vantage - 17.05 - MERGE Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
MERGE [ with_isolated_loading ] [ INTO ] target_table [ [AS] correlation_name ]

  USING { { VALUES ( using_expression [,...] ) |
            ( subquery )
          } [AS] source_table_name |

          source_table_name [AS] correlation_name
        } [ ( column_name [,...] ) ]

  ON match_condition { when_matched [ when_not_matched ] | when_not_matched }

  [ LOGGING [ALL] ERRORS [ WITH { NO LIMIT | LIMIT OF error_limit } ] ] [;]
You can specify when_matched and when_not_matched in reverse order.

Syntax Elements

with_isolated_loading
WITH [NO] [CONCURRENT] ISOLATED LOADING
target_table
The base data table, global temporary table, volatile table, or queue table to:
  • update rows in or delete rows from.
  • insert rows into.
The target table must have a primary index and can be row partitioned but cannot be column partitioned.
correlation_name
Optional correlation name for the source table specified by using_expression, subquery, or source_table_name.
using_expression
An expression defining the columns of the table expression that specifies the source table rows for the merge as a list of using_expression values.
The table expression representing the source table rows is composed of the comma-separated list of using_expression values. It is not a single value expression.
using_expression can reference any of the following:
  • Constants
  • Built-in functions. For details, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
  • USING request modifier variables
  • Macro parameters
  • Stored procedure parameters
  • Host variables
  • String literals
  • The target table for the MERGE operation.

    For example, you can use the target table as the source table, but in that case, the target table becomes another instance table that contains the row data as it was prior to the update to the target table by the MERGE operation (see Example: Using the Target Table as the Source Table).

using_expression cannot reference columns in any other base table.
subquery
A subquery table expression that specifies the source table for the merge.
You can specify host variables in the WHERE clause, but not in the select list, of subquery.
All host variables must be preceded by a COLON character.
If subquery returns no rows, the source table is empty, no triggers are fired (see MERGE as a Triggering Action), and the merge operation performs neither update nor insert operations on target_table.
subquery can reference any of the following:
  • Rows in the queried table
  • Constants
  • USING request modifier variables
subquery cannot specify an ORDER BY clause.
The WHERE clause for subquery must include an equality condition on the UPI or USI of the queried table to ensure a singleton select.
source_table_name
Name of the source table to be merged into target_table.
column_name
Optional names to be used for the source row columns defined by the corresponding using_expression or subquery select list expression or by source_table_name.
If you do not specify any column names, MERGE uses the column names specified in the using_expression or subquery of the USING clause.
Source row columns, qualified by source_table_name, can be referenced in match_condition or in an update_expression or insert_expression.
match_condition
Conditional expression that determines whether the source row matches a given row in the target table. If the condition is met for any target rows and a WHEN MATCHED clause is specified, then the matching target rows are updated or deleted.
Match_condition must specify an equality constraint on the primary index of target_table to ensure that the candidate target row set can be hash-accessed on a single AMP. The specified primary index value must match the primary index value implied by the column values specified in the WHEN NOT MATCHED clause.
If the primary index value is the result of a using_expression, the expression cannot reference any column in target_table. Additionally, match_condition cannot specify subqueries or references to columns that do not belong to either the source table or to target_table.
If target_table is a row-partitioned table, the values of the partitioning columns must also be specified in match_condition, and the WHEN NOT MATCHED clause must specify the same partitioning column values as match_condition.
Host variables are permitted in match_condition.
All host variables must be preceded by a COLON character.
match_condition cannot reference a table that is neither the target table nor the source table.
when_matched
WHEN MATCHED THEN { { UPDATE | UPD } SET update_spec [,...] | DELETE }
when_not_matched
WHEN NOT MATCHED THEN { INSERT | INS }
  { VALUES | ( insert_column [,...] VALUES ) }
  ( insert_expression [,...] )
LOGGING [ALL] ERRORS
Log all data errors, reference index errors, and USI errors.
If you do not specify the LOGGING ERRORS option, the system does no error handling. If an error occurs, the following session-mode behavior occurs:
  • If the current session mode is ANSI, then the erring request rolls back.
  • If the current session mode is Teradata, then the erring transaction rolls back.
The optional keyword ALL is the default.
WITH NO LIMIT
that there is no limit to the number of errors that can be logged in the error table associated with the target data table for this MERGE operation.
Note that this does not mean that there is no limit on the number of errors the system can log for a MERGE request; instead, it means that errors will continue to be logged until the system-determined limit of 16,000,000 errors have been logged. See CREATE ERROR TABLE in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.
WITH LIMIT OF error_limit
that the limit on the number of errors that can be logged in the error table associated with the target data table for this MERGE operation is error_limit.
If this limit is exceeded, the system aborts the request in Teradata session mode or the transaction in ANSI session mode, and rolls back all changes made to the target table, but does not roll back the logged error table rows.
The value you specify for error_limit can be anything in the range from 1 through 16,000,000, inclusive. The default value for error_limit is 10.
WITH [NO] ISOLATED LOADING
Without NO, MERGE can be performed as a concurrent load isolated operation.
With NO, MERGE is not performed as a concurrent load isolated operation.
CONCURRENT
Optional keyword that you can include for readability.
update_spec
update_column = update_expression
An equality condition on a target table column (specified by update_column) that defines how the specified field is to be updated.
update_expression produces a new value to be placed into the field to be updated.
update_expression can include source table column references, target table column references, a constant, a null expressed by the reserved word NULL, a DEFAULT function, host variables, or an arithmetic expression for calculating the new value.
All host variables must be preceded by a COLON character.
You cannot specify a derived period column name.
DELETE
Matching rows are deleted.
DELETE cannot be combined with INSERT or UPDATE in a MERGE statement.
INSERT
INS
An introduction to a value list to be inserted for nonmatching rows.
Because an inserted row might be a duplicate of an existing row in target_table, its acceptance depends on whether target_table is defined to be SET or MULTISET.
insert_column
Column name into which a corresponding value in the value list is to be inserted for nonmatching rows.
insert_expression
Value to be inserted into a corresponding insert_column for nonmatching rows.
Host variables are permitted in insert_expression.
All host variables must be preceded by a COLON character.