UPDATE (Upsert Form) - Teradata Database

UPDATE (Upsert Form)

Purpose  

Updates column values in a specified row and, if the row does not exist, inserts it into the table with a specified set of initial column values.

For details on the temporal form, see Temporal Table Support.

Syntax  

where:

ANSI Compliance

The upsert form of UPDATE is a Teradata extension to the ANSI SQL:2011 standard.

Required Privileges

The following privilege rule applies to both the UPDATE and INSERT portions of this statement:

The following privilege rules applies to the UPDATE portion of this statement:

The following privilege rules apply to the INSERT portion of this statement:

Also, the immediate owner of the view (that is, the database in which the view resides) must have the INSERT privilege on the underlying object (view or base table).

UDTs and UPDATE (Upsert Form)

UDT expressions can be used in an upsert form of UPDATE statement in any way that is consistent with the general support of UDTs by INSERT and UPDATE and with existing semantics for UPSERT (see “Inserting Into Distinct UDT Columns” on page 375, “Inserting Into Structured UDT Columns” on page 376, “Updating Distinct UDT Columns” on page 500, and “Updating Structured UDT Columns” on page 501.

The UPDATE statement used in the upsert operation can also use a mutator SET clause if it updates a structured UDT column (see “Updating Structured UDTs Using a Mutator SET Clause” on page 502).

Large Objects and UPDATE (Upsert Form)

The behavior of truncated LOB updates differs in ANSI and Teradata session modes. The following table explains the differences in truncation behavior.

UPDATE (Upsert Form) Insert Operations and Partitioned Primary Indexes

The rules for the INSERT component of the Upsert form of an UPDATE statement are as follows:

However, you can specify a Period column that is not defined as part of a partitioning expression for both equality and inequality conditions on that column for an upsert request.

If both BEGIN and END bounds are specified in the partitioning expression of the table, then only an equality condition on both bounds is treated as a bound matching condition. This must result in a single partition.

The process for this is:

a The system checks the WHERE clause condition for its truth value after the row insert.

b The system evaluates the condition, then does one of the following:

c If the condition evaluates to FALSE, the system deletes the row from the sparse join index.

d If the condition evaluates to FALSE, the system retains the row in the sparse join index.

UPDATE (Upsert Form) Update Operations and Partitioned Primary Indexes

The rules for the UPDATE component of the Upsert form of an UPDATE statement are as follows.

You cannot submit an UPDATE request to:

You must specify the same partition of the combined partitioning expression for the INSERT component in the upsert form of an UPDATE request that you specify for the UPDATE component.

You must specify the values of the partitioning columns in the WHERE clause of the upsert form of an UPDATE request.

You also cannot modify the values of those partitioning columns; otherwise, the system aborts the request and returns an error message to the requestor.

Updating a base table row does not always cause inserts or updates to a join index on that base table. For example, you can specify a WHERE clause in the CREATE JOIN INDEX statement to create a sparse join index for which only those rows that meet the condition of the WHERE clause are inserted into the index, or, for the case of a row in the join index being updated in such a way that it no longer meets the conditions of the WHERE clause after the update, cause that row to be deleted from the index.

The process for this activity is as follows:

1 The system checks the WHERE clause condition for its truth value after the update to the row.

2 The system evaluates the new result of the partitioning expression for the updated row.

You cannot update the system‑derived columns PARTITION and PARTITION#L1 through PARTITION#L15.

Expression evaluation errors, such as divide by zero, can occur during the evaluation of a partitioning expression. The system response to such an error varies depending on the session mode in effect at the time the error occurs.

Design your partitioning expressions so that expression errors cannot, or are very unlikely to, occur.

When the target table of an insert operation is a PPI table, the INSERT clause must specify the same partitioning column values as the UPDATE clause. This also applies when the UPDATE condition specifies conditions based on Period bound functions.

If you specify a Period column as part of the partitioning expression for a target PPI table, you can only specify equality conditions on that column for an upsert request on that table. An upsert request cannot specify inequality conditions on a Period column that is specified in a partitioning expression for the table.

However, an upsert request can specify a Period column that is not defined as part of a partitioning expression for equality and inequality conditions on that column.

If you specify a Period column in an upsert request, it must be a term in an equality condition.

If the partitioning expression for a target PPI table specifies a Period bound function, then an equality condition on the bound function is treated as a PPI bound matching condition.

If BEGIN and END bounds are specified in the partitioning expression of the table, only an equality on both bounds is processed as a bound matching condition. This must result in a single partition.

The conditions IS UNTIL_CHANGED and IS UNTIL_CLOSED are treated as equality conditions only for the END bound function.

UPDATE (Upsert Form) and Subqueries

The Upsert form of UPDATE does not support subqueries.

Queue Tables and UPDATE (Upsert Form)

The best practice is to avoid using the UPDATE statement on a queue table because the operation requires a full table scan to rebuild the internal queue table cache. You should reserve this statement for exception handling.

An UPDATE statement cannot be in a multistatement request that contains a SELECT and CONSUME request for the same queue table.

For more information on queue tables and the queue table cache, see SQL Data Definition Language.

Definition of Upsert

A simple upsert is a standalone update operation for which a subsequent standalone insert operation is coded that is performed only if the specific row to be updated is found not to exist. These actions require the performance of two individual SQL requests in the case where no row is found to update. This was the only means of performing a conditional insert on an update failure prior to the introduction of the upsert form of the UPDATE statement.

The more high-performing atomic upsert operation, represented by the upsert form of the UPDATE statement, is a single SQL request that includes both UPDATE and INSERT functionality. The specified update operation performs first, and if it fails to find a row to update, then the specified insert operation performs automatically.

Purpose of the Atomic Upsert Operation

The single-pass upsert is described as atomic to emphasize that its component UPDATE and INSERT SQL statements are grouped together and performed as a single, or atomic, SQL request.

Existing Teradata Parallel Data Pump scripts that use the upsert feature in its Teradata Parallel Data Pump-specific form, using paired UPDATE and INSERT requests, do not have to be changed to take advantage of the atomic upsert capability because Teradata Parallel Data Pump automatically converts an UPDATE … INSERT request pair in its older syntax into a corresponding atomic upsert request whenever appropriate.

The atomic upsert syntax can also be used by any CLIv2-based applications and is particularly useful when coded directly into BTEQ scripts.

Rules for Using Atomic Upsert

There are three fundamental constraints on an atomic upsert operation that apply universally. The following table defines and explains these constraints.

When you perform an upsert UPDATE to a PPI table, the following rules must be followed or an error is returned:

The outcome of expression evaluation errors of PPI tables, such as divide‑by‑zero errors, depends on the session mode.

UPDATE (Upsert Form) As a Triggering Action

If no rows qualify, then no triggers are fired.

Triggers invoke the following behavior when fired as the result of an UPDATE (Upsert Form) request:

Triggers are fired as if the UPDATE and INSERT components in the atomic upsert request are performed as separate statements: unconditionally for UPDATE and conditionally for INSERT. The INSERT is performed only when no rows qualify for the UPDATE.

UPDATE (Upsert Form) As a Triggered Action

UPDATE (Upsert Form) requests are supported as triggered actions. The rules for their use as triggered actions are the same as for any other SQL statement.

Rules and Restrictions

The basic upsert constraints described in “Rules for Using Atomic Upsert” on page 522 follow from the conventional definition of upsert functionality.

Unsupported Syntax or Features

This table lists unsupported syntax or features. Attempt to use them causes an error.

Using UPDATE (Upsert Form) Requests with Scalar Subqueries

The rules for using scalar subqueries with UPDATE (Upsert Form) requests are the same as those for simple UPDATE requests (see “Rules for Using Scalar Subqueries in UPDATE Requests” on page 503) with the exception of the following additional rule:

Using UPDATE (Upsert Form) Requests on Nonpartitioned NoPI Tables and Column-Partitioned Tables

The following rules apply to using UPDATE (Upsert Form) requests on nonpartitioned NoPI tables and column‑partitioned tables.

Using UPDATE (Upsert Form) Requests on PPI Tables

The following rules apply to using UPDATE (Upsert Form) requests on PPI tables.

The optimization to skip the update if the row is unchanged is not done for a column‑partitioned table or join index.

Using UPDATE (Upsert Form) with the DEFAULT Function

The following rules apply to using the DEFAULT function with the upsert form of UPDATE:

The resulting data type of the DEFAULT function is the data type of the constant or built-in function specified as the default unless the default is NULL. If the default is NULL, the resulting date type of the DEFAULT function is the same as the data type of the column or expression for which the default is being requested.

See SQL Functions, Operators, Expressions, and Predicates for more information about the DEFAULT function.

Using the UPDATE (Upsert Form) with NoPI Tables

You cannot use UPDATE (Upsert Form) requests to update NoPI table rows.

See Database Design for more information about NoPI tables.

Examples

This section provides examples that show how the atomic upsert form of UPDATE works, including error cases. All examples use the same table called sales, which is defined as:

     CREATE TABLE sales, FALLBACK (

       item_nbr   INTEGER NOT NULL,

       sale_date  DATE FORMAT 'MM/DD/YYYY' NOT NULL,

       item_count INTEGER)

     PRIMARY INDEX (item_nbr);

Assume that the table has been populated with the following data.

     INSERT INTO sales (10, '05/30/2000', 1);

Example : Upsert Update

This example shows a valid upsert UPDATE request.

     UPDATE sales 

     SET itemcount = item_count + 1 

     WHERE (item_nbr = 10 

     AND    sale_date = '05/30/2000') 

     ELSE INSERT 

     INTO sales (10, '05/30/2000', 1);

After all of the rules have been validated, the row with item_nbr = 10 and sale_date = ‘05/30/2000’ gets updated.

A message noting the successful update of one row returns to the user.

Example : Upsert Insert

This example shows a valid upsert INSERT request.

     UPDATE sales 

     SET item_count = item_count + 1 

     WHERE (item_nbr = 20 

     AND    sale_date = '05/30/2000') 

     ELSE INSERT INTO sales (20, '05/30/2000', 1);

After all of the rules have been validated and no row was found that satisfies the compound predicate item = 20 and sale_date = ‘05/30/2000’ for the update, a new row is inserted with item_nbr = 20.

A message noting the successful insert of one row returns to the user.

Example  

This example shows an upsert UPDATE request that does not specify the same table name for both the UPDATE part and the INSERT part of the request.

     UPDATE sales 

     SET item_count = item_count + 1 

     WHERE (item_nbr = 10 

     AND    sale_date = '05/30/2000') 

     ELSE INSERT INTO new_sales (10, '05/30/2000', 1);

One of the rules of the upsert form of UPDATE is that only one table is processed for the request. Because the tables sales and new_sales are not the same for this example the system returns an error to the user indicating that the name of the table must be the same for both the UPDATE and the INSERT.

Example  

This example shows an upsert UPDATE request that does not specify the same primary index value for the UPDATE and INSERT parts of the request.

     UPDATE sales 

     SET item_count = item_count + 1 

     WHERE (item_nbr = 10 

     AND    sale_date = '05/30/2000') 

     ELSE INSERT INTO sales (20, '05/30/2000', 1);

The primary index value specified for the UPDATE and the INSERT must be the same. Otherwise, the operation is looking at two different rows: one for UPDATE and the other for the INSERT. This is not the purpose of the upsert form of UPDATE.

Because the specified primary index values of 10 and 20 are not the same, this case returns an error to the user, indicating that the primary index value must be the same for both the UPDATE and the INSERT.

Example

This example shows an upsert UPDATE request that does not specify the primary index in its WHERE clause.

     UPDATE sales 

     SET item_count = item_count + 1 

     WHERE sale_date = '05/30/2000' 

     ELSE INSERT INTO sales (10, '05/30/2000', 1);

When the primary index is not specified in the UPDATE portion of an upsert request, the operation could have to perform an all-row scan to find rows to update. This is not the purpose of upsert form of UPDATE. This case returns an error.

Example

This example shows an upsert UPDATE request that fails to specify the ELSE keyword.

     UPDATE sales 

     SET item_count = item_count + 1 

     WHERE (item_nbr = 10 

     AND    sale_date = '05/30/2000') 

     INSERT INTO sales (10, '05/30/2000', 1);

This case returns a syntax error to the user.

Example : Upsert Update Using the DEFAULT Function

When the DEFAULT function is used for either the UPDATE operation or for the INSERT operation within the upsert, it evaluates to the default value of the referenced column. This is a Teradata extension.

Assume the following table definition for the examples:

     CREATE TABLE table_19 (

       col_1 INTEGER, 

       col_2 INTEGER DEFAULT 10,

       col_3 INTEGER DEFAULT 20,

       col_4 CHARACTER(60));         

     UPDATE table19 

       SET col_2 = DEFAULT 

         WHERE col1 = 10

       ELSE INSERT table_19 (10, DEFAULT, DEFAULT, 'aaa');

This request updates col_2 to the DEFAULT value of col_2, which is 10, depending on whether the WHERE condition evaluates to true or not.

If the row does exist, the updated row becomes the following: (10, 10, existing value, existing value).

If the row does not exist, the system inserts a new row with the a col_2 value of 10 (the default value of col_2) and a col_3 value of 20 (the default value of col_3). The newly inserted row is as follows: (10, 10, 20, 'aaa').

The following example is a correct use of the DEFAULT function within an UPDATE upsert request:

     UPDATE table_19 

       SET col_2 = DEFAULT(col3) 

         WHERE col_1 = 10

       ELSE INSERT table_19 (10, DEFAULT, DEFAULT(col_2), 'aaa');

When the value of col_1 is 10, this upsert updates col_2 to the DEFAULT value of col_3, which is 20, because the column name passed as the argument of the DEFAULT function is col_3.

If the row does exist, the updated row becomes the following: (10, 20, existing value, existing value).

If the row does not exist, the system inserts a new row with a col_2 value of 10 (default value of col_2) and a col_3 value of 10 (the default value of col_2). The newly inserted row is as follows: (10, 10, 10, 'aaa').

Example : Upsert Update Using a Period Bound Function

Suppose you define the following PPI table using the END Period bound function.

     CREATE SET TABLE testing.t33 (

       a INTEGER,

       b PERIOD(DATE),

       c INTEGER)

     PRIMARY INDEX (a)

     PARTITION BY CAST((END(b)) AS INTEGER);

This UPDATE upsert request inserts a new row into t33.

     UPDATE t33 

       SET c = 1

         WHERE a = 20 

         AND   END(b) = DATE '1901-02-25'

       ELSE INSERT INTO t33 (20, PERIOD(DATE '1901-02-24', 

                             DATE '1901-02-25'), 1);

Related Topics