UPDATE (Positioned Form) | Teradata Vantage - UPDATE (Positioned Form) - Advanced SQL Engine - Teradata Database

Purpose

Updates the most current fetched cursor row.

Invocation

Executable.

Stored procedures and embedded SQL.

Syntax

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

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 referred to as range variables.

column_name = expression

A column name and value with which to update.

When host variables are used in the SET clause, they must always be preceded by a colon character.

cursor_name

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

ANSI Compliance

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

Authorization

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

When executing 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.

UPDATE With Correlated Subqueries

See “Correlated Subqueries and the UPDATE Statement” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146 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.

Update of GENERATED ALWAYS Identity Columns and 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 in embedded SQL, 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 currently 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 currently fetched row of cursor_name or updates followed by a delete of the currently 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 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. Instead, it must be specified by itself. 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 Topics