Syntax Elements - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Isolated Loading Options

WITH ISOLATED LOADING
The MERGE can be performed as a concurrent load isolated operation.
NO
The MERGE is not performed as a concurrent load isolated operation.
CONCURRENT
Optional keyword that you can include for readability.

Target Table Options

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.

USING Clause

USING
An introduction to the table expression that defines the source table rows for the merge operation.
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 “Built-In Functions” in SQL Functions, Operators, Expressions, and Predicates .
  • 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.
AS correlation_name
Optional correlation name for the source table specified by using_expression, subquery, or source_table_name.

ON Clause

ON 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 THEN
Introduction to the operation to be performed on matching rows.
You can specify WHEN MATCHED THEN and WHEN NOT MATCHED THEN clauses in any order.
UPDATE SET
UPD SET
an update set clause operation to be performed for matching rows.
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.
Note: DELETE cannot be combined with INSERT or UPDATE in a MERGE statement.
WHEN NOT MATCHED THEN
An introduction to the operation to be performed on nonmatching rows.
You can specify WHEN MATCHED and WHEN NOT MATCHED clauses in any order. You can also specify a WHEN NOT MATCHED clause without also specifying a WHEN MATCHED clause.
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.
VALUES
Optional keyword for the value list.
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.

Error Logging Options

If you do not specify a value for the LIMIT option, the system defaults to a 10 error limit.

LOGGING ERRORS
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 SQL Data Definition Language .
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.

ANSI Compliance

MERGE is ANSI SQL:2011-compliant.

Note that in the ANSI definition, this statement is named MERGE INTO, while in the Teradata definition, INTO is an optional keyword.