Usage Notes: UPDATE - Teradata Database

Locks and Concurrency

An UPDATE operation sets a WRITE lock for the table, partitions, or row being updated. For a nonconcurrent isolated update on load isolated table, the update operation sets an EXCLUSIVE lock.

The lock set for SELECT subquery operations depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control field, and whether the subquery is embedded within a SELECT operation or within an UPDATE request.

Transaction Isolation Level	DBS Control AccessLockForUncomRead Field Setting	Default Locking Severity for Outer SELECT and Ordinary SELECT Subquery Operations	Default Locking Severity for SELECT Operations Embedded Within an UPDATE Request
SERIALIZABLE	FALSE	READ	READ
SERIALIZABLE	TRUE		READ
READ UNCOMMITTED	FALSE		READ
READ UNCOMMITTED	TRUE		ACCESS

For more information, see:

“SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL” in SQL Data Definition Language
SQL Request and Transaction Processing
Utilities

Activity Count

The activity count in the success response (or ACTIVITY_COUNT system variable for a stored procedure) for an UPDATE request reflects the total number of rows updated. If no rows qualify for update, then the activity count is zero.

Duplicate Rows and UPDATE

It is not possible to distinguish among duplicate rows in a MULTISET table. Because of this, when a WHERE condition identifies a duplicate row, all of the duplicate rows are updated.

Duplicate Row Checks

Unless a table is created as MULTISET (and without UNIQUE constraints) to allow duplicate rows, the system always checks for duplicate rows during the update process. The order in which updates are executed can affect the result of a transaction.

Consider the following example:

     CREATE SET TABLE t1 (
       a INTEGER,
       b INTEGER)
     PRIMARY INDEX (a);

     INSERT INTO t1 VALUES (1,1);

     INSERT INTO t1 VALUES (1,2);

     UPDATE t1
     SET b = b + 1
     WHERE a = 1; /* fails */
     UPDATE t1
     SET b = b - 1
     WHERE a = 1; /* succeeds */

The first UPDATE request fails because it creates a duplicate row.

If the order of the UPDATE requests is reversed, then both UPDATE requests succeed because the UPDATE does not result in duplicate rows.

     CREATE SET TABLE t1 (
       a INTEGER,
       b INTEGER)
     PRIMARY INDEX (a);

     INSERT INTO t1 VALUES (1,1);

     INSERT INTO t1 VALUES (1,2);

     UPDATE t1
     SET b = b - 1
     WHERE a = 1; /* succeeds */
     UPDATE t1
     SET b = b + 1
     WHERE a = 1; /* succeeds */

This mode is characteristic of both simple and join updates. Updates that affect primary or secondary index values, on the other hand, are implemented as discrete delete and insert operations.

Large Objects and UPDATE

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

In this session mode …	The following behavior occurs when non-pad bytes are truncated on insertion …
ANSI	an exception condition is raised. The UPDATE fails.
Teradata	no exception condition is raised. The UPDATE succeeds: the truncated LOB is stored.

UPDATE Processing Time

Processing time for an UPDATE operation is longer under the following conditions:

When the FALLBACK option is specified for the table, because the rows in the secondary copy of the table must also be updated.
When a column on which one or more indexes (secondary or primary) are defined is updated.

You can shorten the processing time for an UPDATE operation by using an indexed column in the WHERE clause of the UPDATE request.

Processing time can also vary between different syntaxes used to perform the identical update operation. Use the EXPLAIN modifier to determine which syntax form produces optimal processing time.

Rules for Embedded SQL and Stored Procedures

The following rules apply to the searched form of the UPDATE statement:

If host variable references are used, a COLON character must precede the host variable in the SET and WHERE clauses. The colon is optional otherwise, but strongly recommended.
If the UPDATE request is specified with a WHERE clause and the specified search condition selects no rows, then the value +100 is assigned to SQLCODE and no rows are updated.
The updated row is formed by assigning each update value to the corresponding column value of the original row. The resulting updated row must be NULL for any column that has the NOT NULL attribute or SQLCODE is set to -1002 and the row is not updated.
Update values are set in the corresponding row column values according to the rules for host variables.
If the table identified by table_name is a view specifying WITH CHECK OPTION, all values updated through that view must satisfy any constraints specified in any WHERE clause defined in the view.

Queue Tables and UPDATE

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 .

Rules for Updating Partitioning Columns of a Row-Partitioned Table

The following rules apply to updating the partitioning columns of a row-partitioned table:

For an UPDATE request that attempts to update the partitioning columns, the partitioning expression must result in a value between 1 and the number of partitions defined for that level.
For an UPDATE request that attempts to insert a row, the partitioning expression for that row must result in a value between 1 and the number of partitions defined for that level.
You cannot update the system-derived columns PARTITION and PARTITION#L1 through PARTITION#L62.

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.

Session Mode	Expression evaluation errors roll back this work unit …
ANSI	request that contains the aborted request.
Teradata	transaction that contains the aborted request.

Take care in designing your partitioning expressions to avoid expression errors.

For the update to succeed, the session mode and collation at the time the table was created do not need to match the current session mode and collation. This is because the partition that contains a row to be updated is determined by evaluating the partitioning expression on partitioning column values using the table’s session mode and collation.
In updating rows in a table defined with a character partitioning, if the collation for the table is either MULTINATIONAL or CHARSET_COLL and the definition for the collation has changed since the table was created, the system aborts any request that attempts to update a row in the table and returns an error to the requestor.
If the partitioning expression for a table involves Unicode character expressions or literals and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to update rows in the table and returns an error to the requestor.

Rules for Updating a Table with a Row-Partitioned Join Index

For an UPDATE request that attempts to insert or update a row in a base table that causes an insert into a join index with row partitioning, the partitioning expression for that index row must result in a value between 1 and the number of partitions defined for that level.
For an UPDATE request that attempts to insert or update a row in a base table that causes an update of an index row in a join index with row partitioning, the partitioning expression for that index row after the update must result in a value between 1 and the number of partitions defined for that level.

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:

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

Condition Result	Description
FALSE	System deletes the row from the sparse join index.
TRUE	System retains the row in the sparse join index and proceeds to stage b.

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

Partitioning Expression	Result
evaluates to null, or is an expression that is not CASE_N or RANGE_N	Not between 1 and 65535 for the row. The system aborts the request. It does not update the base table or the sparse join index, and returns an error.
evaluates to a value, and is an expression that is not CASE_N or RANGE_N	Between 1 and 65535 for the row. The system stores the row in the appropriate partition, which might be different from the partition in which it was previously, and continues processing requests.

In updating rows in a table defined with a character partitioning, if a noncompressed join index with a character partitioning under either an MULTINATIONAL or CHARSET_COLL collation sequence is defined on a table and the definition for the collation has changed since the join index was created, Teradata Database aborts any request that attempts to update a row in the table and returns an error to the requestor whether the update would have resulted in rows being modified in the join index or not.
If the partitioning expression for a noncompressed join index involves Unicode character expressions or literals and the system has been backed down to a release that has Unicode code points that do not match the code points that were in effect when the table or join index was defined, Teradata Database aborts any attempts to update rows in the table and returns an error to the requestor.
You cannot update a base table row that causes an insert into a join index with row partitioning such that a partitioning expression for that index row does not result in a value between 1 and the number of row partitions defined for that level.
You cannot update a base table row that causes an update of an index row in a join index with row partitioning such that a partitioning expression for that index row after the update does not result in a value between 1 and the number of row partitions defined for that level.

Rules for Updating Column-Partitioned Tables

The update may be made in place if the columns being updated are only in ROW format column partitions of a nontemporal column-partitioned table, instead of being treated as a delete of the old row and an insert of the new row. This includes row-level security constraints that are implicitly updated due to the row being updated. An update in place can be done, with the following restrictions:

A column of a primary AMP index or primary index must not be updated.
A column of a unique secondary index defined on the target table must not be updated. Note that columns of a unique join index (UJI) defined (explicitly or implicitly) on the target table may be updated.
All columns of any updated nonunique secondary index defined on the target table must belong to the same target column partition.
A partitioning column must not be updated.
All columns of any updated foreign or parent keys must belong to the same target column partition.
All columns referenced in a check constraint where at least one of these columns is updated must all belong to the same target column partition.
Any source column values from the target table used in the expression for the value to assign to a target column must come from the same target column partition as the target column.
Cannot include updatable cursors.

Identity Columns and UPDATE

You cannot update a GENERATED ALWAYS identity column.

Updating of GENERATED ALWAYS Identity Columns and PARTITION Columns

You cannot update the following set of system-generated columns:

GENERATED ALWAYS identity columns
PARTITION
PARTITION#L n

You can update a GENERATED BY DEFAULT identity column. The specified value is not constrained by identity column parameters. It is constrained by any CHECK constraints defined on the column.

Updating Distinct UDT Columns

To update a distinct type column, either of the following must be true:

The updated value must be of the same distinct type.
There must exist a cast that converts the type of the updated value to the distinct type of the column and the cast is defined with the AS ASSIGNMENT option (see CREATE CAST in SQL Data Definition Language ).
By default, a distinct type has a system-generated cast of this type.

For example, suppose you have the following table definition:

     CREATE TABLE table_1 (
      column1 euro,
      column2 INTEGER)
     UNIQUE PRIMARY INDEX(column2);

Then the information in the following table is true:

Example	Comment
UPDATE table_1 SET column1 = 4.56 WHERE column2 = 5;	Valid if there is a cast defined with the AS ASSIGNMENT option, either system-generated or user-defined, that converts DECIMAL to euro.
UPDATE table_1 SET column1 = CAST(4.56 AS euro) WHERE column2 = 5;	Valid if there is a cast defined with or without the AS ASSIGNMENT option, either system-generated or user-defined, that converts DECIMAL to euro.
USING (price decimal(6,2)) UPDATE table_1 SET column1 = (CAST (:price AS euro)).roundup(0);	Valid if the roundup() method returns the euro type and if there is an appropriate cast definition, either system-defined or user-defined, that converts DECIMAL to euro. Because an explicit cast operation is used, the UDT cast need not have been defined using the AS ASSIGNMENT option. Host variable :price is converted to euro. The roundup() method is invoked.
UPDATE table_1 SET column1 = column1.roundup(0);	Valid if the roundup() method returns the euro type.
UPDATE table_1 SET column1 = column3;	Valid column reference.
UPDATE table_1 SET column1 = NULL WHERE column2 = 10;	Valid setting of a distinct column to NULL.

Updating Structured UDT Columns

To update a structured type column, the updated value must be of the same structured type. For example, suppose you have the following table definition:

     CREATE TABLE table_1 (
       column_1 address,
       column_2 INTEGER
       column_3 address)
     UNIQUE PRIMARY INDEX(column2);

Then the information in the following table is true:

Example	Comment
UPDATE table_1 SET column_1 = '17095 Via Del Campo;92127';	Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address.
USING (addr varchar(30), c2 INTEGER) UPDATE table_1 SET column_1 = :addr WHERE column_2 = :c2;	Valid if there is a cast with AS ASSIGNMENT that converts a string to the structured type address.
UPDATE table_1 SET column_1 = NEW address('17095 Via Del Campo', '92127');	Valid invocation of a constructor method.
USING (street VARCHAR(20), zip CHARACTER(5)) UPDATE table_1 SET column_1 = NEW address(:street,:zip);	Valid invocation of a constructor method with host variables.
UPDATE table_1 SET column_1 = NEW address();	Valid invocation of the constructor function with the NEW syntax.
UPDATE table_1 SET column_1 = (NEW address().street('17087 Via Del Campo').zip('92127'));	Valid mutator invocations: The constructor function is invoked. The result is an address value whose attribute values are set to their defaults. The mutator for the street attribute is invoked. The result is an updated address value with its street attribute modified. The mutator for the zip attribute is invoked. The result is another updated address value with its zip attribute modified. This result also contains the change to the street attribute. The result address value is used to update column_1 of table_1.
UPDATE table_1 SET column_1 = column1.zip('92128');	Valid invocation of a mutator to update the zip attribute.
UPDATE table_1 SET column_1 = (CAST ('17095 Via Del Campo;92127' AS address) );	Valid if the cast from VARCHAR to structured type address is valid.
UPDATE table_1 SET column_1 = column3;	Valid column reference.
UPDATE table_1 SET column_1 = NULL;	Valid setting of a structured type column to NULL.

Updating Structured UDTs Using a Mutator SET Clause

Mutator SET clauses provide a syntax for updating structured type columns. A mutator SET clause can only be used to update structured UDT columns (the specified column_name in a mutator SET clause must identify a structured UDT column). Each mutator method name you specify must be a valid mutator method name for the respective structured type value.

A mutator method name is the same name as the attribute name that it modifies. Within the mutated set clause, parentheses following the attribute name are not valid.

There is one additional restriction on mutator SET clauses.

Consider the following example:

     SET mycol.R = x,
         mycol.y = mycol.R() + 3

As implemented by Teradata, any column references in an expression refer to the value of the column in the row before the row is updated. The system converts the two example clauses to the following single equality expression:

     mycol = mycol.R(x).y(mycol.R() + 3)

This is a deviation from the ANSI SQL:2011 standard.

According to the ANSI SQL:2011 standard, the column reference to mycol in the second example equality expression of the mutator SET clause should reflect the change made to it from the first equality expression of the mutator SET clause, the assignment of x.

The two equality expressions are converted to the following single equality expression:

     mycol = mycol.R(x).y(mycol.R(x).R() + 3)

Rules for Updating Rows Using Views

To update rows using a view through which the table is accessed, observe the following rules:

You must have the UPDATE privilege on the view.
The immediate owner of the view (that is, the containing database for the view) must have the UPDATE privilege on the underlying object (view, base table, or columns) whose columns are to updated, and the SELECT privilege on all tables that are specified in the WHERE clause.
Each column of the view must correspond to a column in the underlying table (that is, none of the columns in the view can be derived using an expression).
The data type definitions for an index column should match in the view definition and in the base table to which the view refers.
Although you can generally convert the data type of a view column (for example, from VARCHAR to CHARACTER), if that converted column is a component of an index, then the Optimizer does not use that index when the base table is updated because the data type of the recast column no longer matches the data type of the index column.

The resulting all-AMP, all-row scan defeats the performance advantages the index was designed for.
No two view columns can reference the same table column.
If the request used to define a view contains a WHERE clause WITH CHECK OPTION, then all values inserted through that view must satisfy the constraints specified in the WHERE clause.
If a view includes a WHERE clause and does not specify WITH CHECK OPTION, then data can be inserted through the view that will not be visible through that view.

Rules for Using Scalar Subqueries in UPDATE Requests

The following rules apply to using scalar subqueries in UPDATE requests:

You can specify scalar subqueries in the FROM and WHERE clauses of an UPDATE request in the same way as you would for a SELECT request. See Scalar Subqueries. You can only specify a scalar subquery in the FROM clause of an UPDATE request as an expression within a derived table. You cannot, however, code a derived table as a scalar subquery.
You can specify scalar subqueries in the SET clause of an UPDATE request.
When you specify a correlated scalar subquery in the SET clause, even if the request has no FROM clause, Teradata Database treats the update as a joined update. See Example: UPDATE With a Scalar Noncorrelated Subquery in its SET Clause.
You can specify an UPDATE statement with scalar subqueries in the body of a trigger.
However, Teradata Database processes any noncorrelated scalar subqueries specified in the FROM, WHERE, or SET clauses of an UPDATE statement in a row trigger as a single-column single-row spool instead of as a parameterized value.

Rules for Using a Scalar UDF in an UPDATE Request

A scalar UDF can be invoked from both the SET clause and the WHERE clause of an UPDATE request. See Example: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses.

Clause	Usage rules are the same as those for invoking a scalar UDF from the …
SET	Select list of a SELECT request. You must use the alias to reference the result of a scalar UDF invocation that has an alias.
WHERE	WHERE clause of a SELECT request. The scalar UDF must be invoked from within an expression that is specified as the search condition. Otherwise, the system returns an error. See WHERE Clause.

Clause

Usage rules are the same as those for invoking a scalar UDF from the …

SET

Select list of a SELECT request.

You must use the alias to reference the result of a scalar UDF invocation that has an alias.

WHERE

WHERE clause of a SELECT request.

The scalar UDF must be invoked from within an expression that is specified as the search condition. Otherwise, the system returns an error.

See WHERE Clause.

Rules for Using the DEFAULT Function With Update

The following rules apply to using the DEFAULT function with an UPDATE statement:

The DEFAULT function takes a single argument that identifies a relation column by name. The function evaluates to a value equal to the current default value for the column. For cases where the default value of the column is specified as a current built-in system function, the DEFAULT function evaluates to the current value of system variables at the time the request is executed.
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.
The DEFAULT function has two forms. It can be specified as DEFAULT or DEFAULT ( column_name ) . When no column name is specified, the system derives the column based on context. If the column context cannot be derived, the request aborts and an error is returned to the requestor.
You can specify a DEFAULT function without a column name argument as the expression in the SET clause. The column name for the DEFAULT function is the column specified as the column_name. The DEFAULT function evaluates to the default value of the column specified as column_name.
You cannot specify a DEFAULT function without a column name argument as part of the expression. It must be specified by itself. This rule is defined by the ANSI SQL:2011 specification.
You can specify a DEFAULT function with a column name argument in the source expression. The DEFAULT function evaluates to the default value of the column specified as the input argument to the DEFAULT function.
For example, DEFAULT(col2) evaluates to the default value of col2. This is a Teradata extension to the ANSI SQL:2011 specification.
You can specify a DEFAULT function with a column name argument anywhere in an update expression. This is a Teradata extension to the ANSI SQL:2011 specification.
When no explicit default value has been defined for a column, the DEFAULT function evaluates to null when that column is specified as its argument.

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

Rules for Using a PERIOD Value Constructor With UPDATE

See SQL Functions, Operators, Expressions, and Predicates for the rules on using PERIOD value constructors. Also see Example: INSERT Using a PERIOD Value Constructor for two examples of how to use PERIOD value constructors in UPDATE requests.

Nonvalid Uses of UPDATE

An UPDATE operation causes an error message to be returned when any of the following conditions occur:

The operation attempts to update a field using a value that violates a constraint (for example, UNIQUE or referential) declared for the column.
The operation attempts to update a field using a value that is of a different numeric type than that declared for the column and the value cannot be converted correctly to the correct type.
The operation attempts to update a VARCHAR column, and that operation causes the row to become identical to another row (except for the number of trailing pad characters), for a table not permitting duplicate rows.
The operation attempts to update a CHARACTER column with a value that is not in the repertoire of the destination character data type.
If in ANSI session mode, updating character data, where in order to comply with maximum length of the target column, non-pad characters are truncated from the source data. This update is valid in Teradata session mode.
The operation attempts to update a row by using values that will create a duplicate of an existing row, for a table not allowing duplicate rows.
The operation references objects in multiple databases without using fully-qualified names. Name resolution problems may occur if referenced databases contain tables or views with identical names and these objects are not fully qualified. Name resolution problems may even occur if the identically named objects are not themselves referenced.
A JSON entity reference was used in the target portion of the SET clause.

FROM Clause and UPDATE

The optional FROM list included in the UPDATE syntax is a Teradata extension to support correlated subqueries and derived tables in the search conditions for UPDATE.

Specify the FROM clause for the following reasons only:

To provide the outer scope of a table with columns referenced in a subquery, making the subquery a correlated subquery.
To permit references to a derived table.
To specify joined tables for an update operation.

If a table is listed in the FROM clause for the UPDATE and not in the FROM clause for a subquery, then field references in the subquery are scoped at the outer level, making it a correlated subquery.

The following rules apply to the use of correlated subqueries in the FROM clause of an UPDATE request:

If a FROM clause is specified for the UPDATE syntax you are using, then any correlation name used must be specified in the FROM clause.
If a correlation name is specified for the updated table name in the FROM clause, this correlation name, rather than the original name, must be used as the table_name that follows the UPDATE keyword. This FROM clause is optional if no joined tables are specified for an UPDATE.
If an inner query column specification references an outer FROM clause table, then the column reference must be fully qualified.
If the FROM clause is omitted, you can specify a correlation name for the table_name that follows the UPDATE keyword.

Also see Correlated Subqueries.

UPDATEs With a Join

If a row from the updated table is joined with a row from another table in the FROM clause, and the specified WHERE condition for the request evaluates to TRUE for that row, then the row in the updated table is updated with columns referenced in the SET clause from the joined row.

When an UPDATE request specifies a join operation, the join is more efficient if the WHERE condition uses values for indexed columns in the joined tables.

Be aware that the order in which join updates are executed can affect the result of a transaction. See Duplicate Row Checks.

UPDATE Support for Load Isolated Tables

A nonconcurrent load isolated update operation on a load isolated table updates the matched rows in-place.

A concurrent load isolated update operation on a load isolated table logically deletes the matched rows and inserts the rows with the modified values. The rows are marked as deleted and the space is not reclaimed until you issue an ALTER TABLE statement with the RELEASE DELETED ROWS option. See SQL Data Definition Language Syntax and Examples.