UPDATE (Positioned Form) Statement | Teradata VantageCloud Lake - UPDATE (Positioned Form)

Updates the most current fetched cursor row.

ANSI Compliance

The positioned form of UPDATE is ANSI/ISO SQL:2011-compliant.

Required Privileges

You must have the UPDATE privilege on the table or columns to be updated.

When running an UPDATE that also requires READ access to an object, you must have the SELECT right to data being accessed.

For example, in the following statement, READ access is required by the WHERE condition.

UPDATE table_1
SET field_1=1
WHERE field_1<0;

Similarly, the following statement requires READ access because you must read field_1 values from table_1 to compute the new values for field_1.

UPDATE table_1
SET field_1 = field_1 + 1;

An UPDATE operation sets a WRITE lock for the table or row being updated.

The activity count in the success response for an UPDATE statement reflects the total number of rows updated. If no rows qualify for update, then the activity count is zero.

Invocation

Executable.

Stored procedures and embedded SQL.

Syntax

{ UPDATE | UPD } table_name [ alias_name ]
  SET set_spec [,...]
  WHERE CURRENT OF cursor_name [;]

Syntax Elements

set_spec

column_name = expression

table_name

The name of the table to be updated.

alias_name

An alias for the table name.

Correlation names are also called range variables.

column_name = expression

A column name and value with which to update.

A host variables in the SET clause must be preceded by a colon character (:).

cursor_name

The name of the updatable cursor being used to point to the rows to be updated.

Usage Notes

UPDATE With Correlated Subqueries
See Correlated Subqueries and UPDATE for information about using correlated subqueries with UPDATE statements.

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.

Session Mode	Behavior
ANSI	When non-pad bytes are truncated on insertion, an exception condition is raised. The UPDATE fails.
Teradata	When non-pad bytes are truncated on insertion, no exception condition is raised. The UPDATE succeeds: the truncated LOB is stored.

Update of GENERATED ALWAYS Identity Columns and PARTITION
You cannot update the following set of system-generated columns:
- GENERATED ALWAYS identity columns
- PARTITION
You can update a GENERATED BY DEFAULT identity column. The specified value is not constrained by identity column parameters, but is constrained by any CHECK constraints defined on the column.
Support of Mutator SET Clauses
Because UDTs are not supported Lake, the mutator SET clause is not supported for the positioned form of UPDATE.
Rules for UPDATE in ANSI Session Mode
- The WHERE CURRENT OF clause enables a UPDATE statement to act on a data row pointed to by the cursor named in WHERE CURRENT OF cursor_name. Such a cursor is said to be updatable.
- You need not include a specification of intent to update or delete a row when you declare cursor_name.
- Multiple updates of the fetched row of cursor_name or updates followed by a delete of the fetched row of cursor_name are valid.
Rule for Updating Partitioning Columns of a PPI Table
If you are updating a partitioning column for a partitioned primary index, then updates to the partitioning columns must be in a range that permits the partitioning expression to produce, after casting values to INTEGER if the value is not already of that type, a value that is not null between 1 and 65535.
Rules for Using the DEFAULT Function
- 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 statement is run.
  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, DEFAULT and 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 must specify a DEFAULT function without a column name argument by itself, not as part of the expression. This rule is defined by the ANSI/ISO 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/ISO 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/ISO 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.

Example: Using the Cursor to Update the Table

In this example, the name of the cursor used to update the table is cursor_01.

EXEC SQL
 UPDATE table_1
 SET text = :text, K = :I + 1000
 WHERE CURRENT OF cursor_01;

Related Information

General information about the DEFAULT function, see DEFAULT.

UPDATE (Positioned Form) Statement | Teradata VantageCloud Lake - UPDATE (Positioned Form) - Teradata VantageCloud Lake

Lake - Working with SQL