15.00 - UPDATE - Teradata Database

Teradata Database SQL Data Manipulation Language

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1146-015K

UPDATE

Purpose  

Modifies column values in existing rows of a table.

There are five forms of the UPDATE statement.

  • Basic - updates one or more rows from a table.
  • The Basic form can be specified with or without a FROM clause.

  • Basic for Joined Tables
  • The Join Condition form updates rows from a table when the WHERE condition directly references columns in tables other than the one from which rows are to be updated; that is, if the WHERE condition includes a subquery or references a derived table.

  • Positioned - see “UPDATE (Positioned Form)” in SQL Stored Procedures and Embedded SQL.
  • Temporal - see ANSI Temporal Table Support and Temporal Table Support.
  • Upsert - see “UPDATE (Upsert Form)” on page 515.
  • Syntax - Basic Form, No FROM Clause

    Syntax - Basic Form, FROM Clause

    Syntax - Joined Tables Form

    where:

     

    Syntax Element …

    Specifies …

    table_name_1

    the name of the base table, queue table, or derived table to be updated, or the name of a view through which the table is accessed.

    If you specify a correlation name for table_name_1 in the FROM clause, then you must specify that correlation name for the updated table instead of table_name_1.

    See “Example 4: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause” on page 507.

    correlation_name

    an alias for table_name_1.

    You cannot specify a correlation name for the table if the UPDATE statement includes the FROM clause.

    Correlation names are also referred to as range variables.

    The correlation_name option is a Teradata extension to the ANSI SQL:2011 standard.

    FROM

    a keyword introducing a table list of the updated table and any other tables from which field values are taken to update the updated table.

    When you use an UPDATE syntax that requires a FROM clause, you should specify the names of all outer tables in the clause.

    The UPDATE statement FROM clause is a Teradata extension to the ANSI SQL:2011 standard.

    table_name_2

    the name of one or more base tables, queue tables, derived tables, or views.

    table_name_1 must be a member of the table_name_2 table list.

    If you do not specify a correlation name for a table_name_2 list object, or if you define a correlation name for it instead of table_name_1, then table_name_2 cannot be a derived table.

    If any table_name_2 list member is a view, then that view must be updatable.

    If you do not specify a database name, the system assumes the current database.

    correlation_name

    an alias for a member of the table_name_2 table list.

    A correlation name must be specified for at least one of the tables in a self-join operation.

    If you specify a correlation name for table_name_1 in the table_name_2 table list using the joined tables syntax, then you must specify that correlation name instead of the true name for table_name_1. See “Example 4: UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause” on page 507.

    Correlation names are also referred to as range variables.

    SET

    the names of one or more columns whose data is to be updated, and the expressions that are used for update.

    If you are updating a UDT column, then you must use the mutator SET clause syntax (see “Updating Structured UDTs Using a Mutator SET Clause” on page 502).

    column_name

    the name of a column whose value is to be set to the value of the specified expression.

    The column_name field is for a column name only.

    Do not use fully-qualified column name forms such as databasename.tablename.columnname or tablename.columnname.

    You cannot specify a derived period column name.

    mutator_method_name

    the name of a mutator method that is to perform some update operation on column_name.

    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.

    To update a structured UDT column, you must use the mutator SET clause syntax. See “Updating Structured UDTs Using a Mutator SET Clause” on page 502.

    expression

    an expression that produces the value for which column_name is to be updated.

    expression can include constants, nulls (specified by the reserved word NULL), a DEFAULT function, or an arithmetic expression for calculating the new value. Values in a targeted row before the update can be referenced in an expression.

    You can specify a scalar UDF for expression if it returns a value expression.

    For join updates, you can reference columns in expression from rows participating in the join.

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

    WHERE

    a conditional clause. For more information see “WHERE Clause” on page 119.

    You can only specify a scalar UDF for search_condition if it is invoked within an expression and returns a value expression.

    If you specify a WHERE clause, you must have SELECT access on the searched objects.

    search_condition

    the conditional expression to be used for determining rows whose values are to be updated. The condition can reference multiple tables or specify a scalar subquery. See “Scalar Subqueries” on page 142 and “Rules for Using Scalar Subqueries in UPDATE Requests” on page 503 for details.

    ALL

    Indicates that all rows in the specified table are to be updated.

    The ALL option is a Teradata extension to ANSI SQL.

    ANSI Compliance

    UPDATE is ANSI SQL:2011-compliant.

    Required Privileges

    The following privilege rules apply to the UPDATE statement.

  • You must have the UPDATE privilege on the table or columns to be updated.
  • To update any UDT column, you must also have the UDTUSAGE privilege on that column.
  • When executing an UPDATE that also requires READ access to an object, you must have the SELECT privilege on the data being accessed.
  • For example, in the following request, READ access is required by the WHERE condition.

         UPDATE table_1 
         SET column_1 = 1 
         WHERE column_1 < 0;

    Similarly, the following request requires READ access because you must read column_1 values from table_1 in order to compute the new values for column_1.

         UPDATE table_1 
         SET field_1 = column_1 + 1;

    The following request does not require SELECT privileges:

         UPDATE table_1 
         SET column_3 = 0 ALL;

    Locks and Concurrency

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

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

     

    IF the transaction isolation level is …

    AND the DBS Control AccessLockForUncomRead flag is set …

    THEN the default locking severity for outer SELECT and ordinary SELECT subquery operations is …

    AND the default locking severity for SELECT operations embedded within an UPDATE request is …

    SERIALIZABLE

    FALSE

    READ

    READ

    TRUE

    READ

    READ UNCOMMITTED

     

    FALSE

    READ

    TRUE

    ACCESS

    For More Information

  • See “SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL” in SQL Data Definition Language.
  • See SQL Request and Transaction Processing.
  • See 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 PPI Table

    The following rules apply to updating the partitioning columns of a PPI table:

  • If you submit an UPDATE request that attempts to update the partitioning columns of a table with row partitioning such that the partitioning expression does not result in a value between 1 and the number of partitions defined for that level, Teradata Database aborts the request and returns an error to the requestor.
  • If you submit an UPDATE request that attempts to insert a row into a table with row partitioning such that a partitioning expression for that row does not result in a value between 1 and the number of partitions defined for that level, Teradata Database aborts the request and returns an error to the requestor.
  • If you submit 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 such that a partitioning expression for that index row does not result in a value between 1 and the number of partitions defined for that level, Teradata Database aborts the request and returns an error to the requestor.
  • If you submit 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 such that a partitioning expression for that index row after the update does not result in a value between 1 and the number of partitions defined for that level, Teradata Database aborts the request and returns an error 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:
  • a The system checks the WHERE clause condition for its truth value after the update to the row.

     

    IF the condition evaluates to …

    THEN …

    FALSE

    the system deletes the row from the sparse join index.

    TRUE

    the system retains the row in the sparse join index and proceeds to stage b.

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

     

    IF the partitioning expression …

    THEN …

  • evaluates to null, or
  • is an expression that is not CASE_N or RANGE_N
  • it's result is 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
  • it's result is 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 stored, and continues processing requests.

  • 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.
  •  

    In this session mode …

    Expression evaluation errors roll back this work unit …

    ANSI

    request that contains the aborted request.

    Teradata

    transaction that contains the aborted request.

    When you design your partitioning expressions, you should construct them in such a way that expression errors either cannot, or are very unlikely to, occur.

  • 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.
  • Collation has the following implications for updating rows in tables defined with a character partitioning:
  • If the collation for a PPI table is either MULTINATIONAL or CHARSET_COLL and the definition for the collation has changed since the table was created, Teradata Database aborts any request that attempts to update a row in the table and returns an error to the requestor.
  • 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 table or 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.
  • Rules for Updating Nonpartitioned NoPI and Column‑Partitioned Tables

    The following rules apply to updating nonpartitioned NoPI tables and column‑partitioned tables:

  • 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.
  • An update for a column-partitioned table or join index is processed as a deletion of the old row followed by an insertion of the updated row.
  • The optimization to skip the update if the row is unchanged is not done for a column‑partitioned table or join index.

    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#Ln
  • 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.

    1 Host variable :price is converted to euro.

    2 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:

    1 The constructor function is invoked. The result is an address value whose attribute values are set to their defaults.

    2 The mutator for the street attribute is invoked. The result is an updated address value with its street attribute modified.

    3 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.

    4 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.

    In other words, 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” on page 142 for details). 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 7: UPDATE With a Scalar Noncorrelated Subquery in its SET Clause” on page 507).
  • 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 12: UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses” on page 509.

     

    FOR this clause …

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

    SET

    select list of a SELECT request.

    See “Invoking a Scalar UDF From a SELECT Statement” on page 37.

    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, Teradata Database aborts the request and returns an error.

    See “WHERE Clause” on page 119.

    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 17: INSERT Using a PERIOD Value Constructor” on page 411 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

    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” on page 134.

    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” on page 494 for more information.

    Example  

    The following request updates the yrsexp column in the employee table for employee Greene, who is employee number 10017:

         UPDATE employee 
         SET yrs_exp = 16 
         WHERE empno = 10017;

    Example  

    The following request updates the employee table to apply a 10 percent cost of living increase to the salary for all employees:

         UPDATE employee 
         SET salary = salary * 1.1 ALL ;

    Example  

    The following request places a null in the salary column for employee number 10001:

         UPDATE employee 
         SET salary = NULL 
         WHERE emp_no = 10001 ;

    Example : UPDATE Specifying a Correlation Name for the Updated Table in the FROM Clause

    Because the following UPDATE request specifies the correlation name e for employee in its FROM clause, you must specify e in place of employee immediately following the UPDATE keyword.

         UPDATE e
         FROM employee AS e, department AS d
         SET salary = salary * 1.05
         WHERE e.emp_no = d.emp_no;

    Example : UPDATE With a Subquery in its WHERE Clause

    The following examples perform the same UPDATE operation using either a subquery join with a derived table or a simple equality join on column_1 in table_1 and table_2.

         UPDATE table_1 
         SET column_1 = 1 
         WHERE column_1 IN (SELECT column_2
                            FROM table_2);
     
         UPDATE table_1 
         SET column_1 = 1 
         WHERE table_1.column_1=table_2.column_1;

    Example : UPDATE With a Noncorrelated Subquery in its WHERE Clause

    The following UPDATE operation uses a subquery to decrease the list price on an electroacoustic CD budget line for labels containing the string ‘erstwhile’:

         UPDATE disc
         SET price = price * .85
         WHERE label_no IN (SELECT label_no
                            FROM label
                            WHERE label_name LIKE '%erstwhile%')
                            AND   line = 'NEA';

    You can obtain the same result by writing the query using a join between the disc and label tables on label_no:

         UPDATE disc
         SET price = price * .85
         WHERE disc.label_no = label.label_no
         AND   label_name LIKE '%erstwhile%'
         AND   line = 'NEA';

    Example : UPDATE With a Scalar Noncorrelated Subquery in its SET Clause

    You can specify a scalar subquery in the same way that you specify a column or constant in the SET clause of an UPDATE request.

    The following example specifies a scalar subquery in its SET clause:

         UPDATE sales_sum_table AS sst 
         SET  total_sales = (SELECT SUM(amount) 
                             FROM sales_table AS s
                             WHERE s.day_of_sale BETWEEN sst.period_start 
                                                 AND     sst.period_end);

    Example : UPDATE With a Scalar Correlated Subquery in its SET Clause

    When a scalar correlated subquery is specified in the SET clause, even if the UPDATE request does not have a FROM clause, Teradata Database treats the update as a joined update because of the scalar correlated subquery.

    The following example requires salesumtable to be joined with salestable, and the update is done using a merge update operation via a spool.

         UPDATE sales_sum_table AS sst 
         SET  total_sales = (SELECT SUM(amount) 
                             FROM sales_table AS s
                             WHERE s.day_of_sale BETWEEN sst.period_start 
                                                 AND     sst.period_end); 

    Example : UPDATE With a Correlated Subquery in its WHERE Clause

    The following correlated subquery is executed once for each row of the outer reference, publisher, and since two publishers have no books in the library two rows of publisher are modified.

         UPDATE publisher 
         SET pub_num = NULL
         WHERE 0 = (SELECT COUNT(*)
                    FROM book 
                    WHERE book.pub_num = publisher.pub_num);

    Two publishers have books in the library, and two publishers do not.

    “Correlated Subqueries” on page 134 describes additional examples of correlated and noncorrelated subqueries.

    Example : UPDATE With a Noncorrelated Subquery in its WHERE Clause

    To modify “Example 9: UPDATE With a Correlated Subquery in its WHERE Clause” on page 508 to be a noncorrelated subquery, change the subquery to include all tables it references in the inner FROM clause.

         UPDATE publisher 
         SET pubnum = NULL 
         WHERE 0 = (SELECT COUNT(*)
                    FROM book, publisher
                    WHERE book.pubnum = publisher.pubnum);

    The request does not contain a correlated subquery and the condition in the subquery has a local defining reference. The count, determined once, is nonzero, and no rows are deleted.

    “Correlated Subqueries” on page 134 describes additional examples of correlated and noncorrelated subqueries.

    Example : UPDATE With a Join

    The following example updates the employee table to give each employee a 5 percent salary increase using a join between the employee and department tables.

         UPDATE employee
         FROM department AS d 
         SET salary = salary * 1.05 
         WHERE employee.dept_no = d.dept_no 
         AND   salary_pool > 25000;

    Example : UPDATE Using an SQL UDF Invocation in the SET and WHERE Clauses

    Assume you have created an SQL UDF named value_expression. The following example updates table t1 using the result of value_expression when it is passed values for columns a1 and b1 to update columns a1 and b1 for rows where the invocation of value_expression using the values for columns c1 and d1 are greater than 5.

         UPDATE t1 
         SET b1 = test.value_expression(t1.a1, t1.b1) 
         WHERE test.value_expression(t1.c1, t1.d1) > 5;

    Example : UPDATE With a Mutator SET Clause

    The mutator SET clause syntax permits you to write UPDATE requests for structured UDTs in a form of shorthand notation. Consider the following structured data type definitions:

         CREATE TYPE person AS (
           last_name  VARCHAR(20),
           first_name VARCHAR(20),
           birthdate  DATE)
         … 
         ;
     
         CREATE TYPE school_record AS (
           school_name VARCHAR(20),
           gpa         FLOAT)
         INSTANTIABLE
         … 
         ;
     
         CREATE TYPE college_record AS (
           school  school_record,
           major   VARCHAR(20),
           minor   VARCHAR(20))
         INSTANTIABLE
         … 
         ;

    Suppose you have the following table definition:

         CREATE TABLE student_record (
           student_id  INTEGER,
           student     person,
           high_school school_record,
           college     college_record);

    Without the mutator SET clause notation, UPDATEs must be written in forms of chained or nested mutator method invocations. For example:

  • The following UPDATE request changes student John Doe to Natsuki Tamura:
  •      UPDATE student_record
          SET student = student.Last_name('Tamura').First_name('Natsuki')
         WHERE student.First_name() = 'John'
         AND   student.Last_name() = 'Doe';
  • The following UPDATE request makes the following updates to the college record of student Steven Smith:
  • school_name = 'UCLA'
  • GPA = 3.20
  • major = 'Computer Science'
  •      UPDATE student_record
          SET college =
          college.school(college.school().school_name('UCLA').GPA(3.20)).      major('Computer Science');
         WHERE student.First_name() = 'Steven'
         AND   student.Last_name() = 'Smith';

    Formulating the necessary chained or nested mutator method invocations can be very complex. However, you can use the mutator SET clause notation to make writing UPDATE requests for a structured type column simpler and more intuitive.

    Consider these rewrites of the two previous UPDATE requests (these use the mutator SET clause notation):

  • The following UPDATE request changes student John Doe to Natsuki Tamura:
  •      UPDATE student_record
           SET student.Last_name = 'Tamura',
               student.First_name = 'Natsuki',
         WHERE student.First_name() = 'John'
         AND   student.Last_name() = 'Doe';
  • The following UPDATE request makes the following updates to the college record of student Steven Smith:
  • school_name = 'UCLA'
  • GPA = 3.20
  • major = 'Computer Science'
  •      UPDATE student_record
          SET college.school.school_name = 'UCLA',
              college.school.GPA = 3.20,
              college.major = 'Computer Science'
         WHERE student.First_name() = 'Steven'
         AND   student.Last_name() = 'Smith';

    Example : Update Requests Using the DEFAULT Function

    You can specify a DEFAULT function without a column name 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.

    The examples below assume the following table definition:

         CREATE TABLE table_11 (
           col_1 INTEGER, 
           col_2 INTEGER DEFAULT 10,
           col_3 INTEGER DEFAULT 20,
           col_4 CHARACTER(60));

    The following UPDATE requests are valid:

    The following example updates the values of col3 to 20 (its default value) for all rows:

         UPDATE table_11 
           SET col_3 = DEFAULT;

    The following example updates the values of Col3 to 20 (its default value) for rows where the value of col1=5.

         UPDATE table_11 
           SET col_3 = DEFAULT 
             WHERE Col1 = 5;

    Assume the following table definition for the next example:

         CREATE TABLE table_12 (
           x INTEGER,
           y INTEGER);

    The following example updates the values of col3 to 20 (its default value) depending on whether the WHERE condition evaluates to true or not.

         UPDATE table_11 
           SET col_3 = DEFAULT 
             WHERE 5 < ANY 
              (SELECT y 
               FROM table_12);

    You can specify a DEFAULT function with a column name in the source expression. The DEFAULT function evaluates to the default value of the column name specified as the input argument to the DEFAULT function. For example, DEFAULT(col_2) evaluates to the default value of col_2. This is a Teradata extension.

    The following UPDATE request is valid. The input argument to the DEFAULT function is col_2. Therefore, the DEFAULT function evaluates to the default value of the col_2 and then set col_3 to this value. Specifically, it updates the values of col_3 to 10 (the default value of col_2) for all rows.

         UPDATE table_11 
           SET col3 = DEFAULT(col_2);

    The following example updates the values of col_3 to 10 (the default value of col_2) for rows where the value of col_1 is 5.

         UPDATE table_11
            SET col_3 = DEFAULT(col_2) 
             WHERE col_1 = 5;

    You can specify a DEFAULT function with a column name anywhere in the update expression. This is a Teradata extension to the ANSI SQL:2011 standard.

    The following UPDATE request is valid. The input argument to the DEFAULT function is col_2; therefore, the DEFAULT function evaluates to the default value of col_2. The request then updates the value of col_3 to 15 (10+5, the default value of col_2 + 5) for all rows.

         UPDATE table_11 
           SET col_3 = DEFAULT(col_2) + 5;

    The following example updates the value of col3 to 15 (the default value of col2+5) for all rows.

         UPDATE table_11 
           SET col_3 = DEFAULT(col_2) + 5 ALL;

    The following example updates the values of col3 to 15 (the default value of col_2 + 5) for rows where the value of col_1=20.

         UPDATE table_11 
           SET col_3 = DEFAULT(col_2)+5 
             WHERE col_1 = 20;

    When there is no explicit default value associated with the column, the DEFAULT function evaluates to null.

    Assume the following table definition for the examples that follow:

         CREATE TABLE table_13 (
           col_1 INTEGER,
           col_2 INTEGER NOT NULL,
           col_3 INTEGER NOT NULL DEFAULT NULL,
           col_4 INTEGER CHECK (col_4>100) DEFAULT 99 );

    In the following example, col_1 is nullable and does not have an explicit default value associated with it; therefore, the DEFAULT function evaluates to null.

         UPDATE table_13 
           SET col_1 = DEFAULT;

    The following UPDATE requests are equivalent. For both requests, the DEFAULT function evaluates to the default value of col_3 for rows where the value of col_1 is 5.

         UPDATE table_11 
           SET col_3 = DEFAULT(c3) 
             WHERE col_1 = 5;
     
         UPDATE table_11 
           SET col_3 = DEFAULT 
             WHERE col_1 = 5;

    Example : UPDATE Using a PERIOD Value Constructor

    The following example uses tables t1 and t2, which are defined as follows:

         CREATE TABLE t1 (
           c1 INTEGER
           c2 PERIOD(DATE))
         UNIQUE PRIMARY INDEX (c1);
     
         CREATE TABLE t2 (
           a INTEGER
           b DATE
           c DATE)
         UNIQUE PRIMARY INDEX (a);
     

    The following two UPDATE requests both use a PERIOD value constructor:

         UPDATE t1
         SET c2 = PERIOD(DATE '2007-02-03', DATE '2008-02-04'));
     
         UPDATE t1 FROM t2
         SET c2 = PERIOD(b,c) 
         WHERE t2.a = 2;

    Example : Updating a NoPI Table

    The following UPDATE request updates the NoPI table nopi012_t1 aliased as t1.

         UPDATE t1 
         FROM nopi012_t1 AS t1, nopi012_t2 AS t2 
         SET c3 = t1.c3 * 1.05 
         WHERE t1.c2 = t2.c2;

    Example : Application of Row-Level Security SELECT and UPDATE Constraints When User Lacks Required Privileges (UPDATE Request)

    This example show how the SELECT and UPDATE constraints are applied when a user that does not have the required privileges submits an UPDATE request in an attempt to update the classification level value for a row. The SELECT constraints filter out the rows that the user is not permitted to access and the UDPATE constraints restrict the user from executing the update operation on the target row.

    The classification level value is stored in the classification_level column, which is one of the constraint columns (the other constraint column is classification_categories).

    An EXPLAIN statement is used to show the steps involved in the execution of the request and the outcome of the application of the constraints.

    Table Definition

    The statement used to create the table in this example is:

         CREATE TABLE rls_tbl(
           col1 INT,
           col2 INT,
           classification_levels   CONSTRAINT,
           classification_categories CONSTRAINT);

    User’s Session Constraint Values

    The user’s sessions constraint values are:

         Constraint1Name LEVELS
         Constraint1Value 2
         Constraint3Name CATEGORIES
         Constraint3Value '90000000'xb

    EXPLAIN Statement

    This EXPLAIN statement is used to show the steps involved in the execution of the UPDATE request and the outcome of the application of the SELECT and UPDATE constraints.

           EXPLAIN UPDATE rls_tbl SET col1=2 where col1=1; 

    EXPLAIN Text

    The system returns this EXPLAIN text.

     *** Help information returned. 28 rows.
     *** Total elapsed time was 1 second.
     
    Explanation
    ---------------------------------------------------------------------------
      1) First, we lock a distinct RS."pseudo table" for write on a RowHash
         to prevent global deadlock for RS.rls_tbl.
      2) Next, we lock RS.rls_tbl for write.
      3) We do a single-AMP RETRIEVE step from RS.rls_tbl by way of the
         primary index "RS.rls_tbl.col1 = 1" with a residual condition of (
         "((SYSLIB.SELECTLEVEL (2, RS.rls_tbl.levels ))= 'T') AND
         ((SYSLIB.SELECTCATEGORIES ('90000000'XB, RS.rls_tbl.categories ))=
         'T')") into Spool 1 (group_amps), which is redistributed by hash
         code to all AMPs.  Then we do a SORT to order Spool 1 by the sort
         key in spool field1.  The size of Spool 1 is estimated with low
         confidence to be 1 row (18 bytes).  The estimated time for this
         step is 0.01 seconds.
      4) We do a group-AMP MERGE DELETE to RS.rls_tbl from Spool 1 (Last
         Use) via the row id.  New updated rows constrained by (
         RS.rls_tbl.levels = SYSLIB.UPDATELEVEL (2, {LeftTable}.levels)), (
         RS.rls_tbl.categories = SYSLIB.UPDATECATEGORIES ('90000000'XB,
         {LeftTable}.categories)) are built and the result goes into Spool
         2 (all_amps), which is redistributed by hash code to all AMPs.
         Then we do a SORT to order Spool 2 by row hash.  The size is
         estimated with low confidence to be 1 row.  The estimated time for
         this step is 22.15 seconds.
      5) We do an all-AMPs MERGE into RS.rls_tbl from Spool 2 (Last Use).
         The size is estimated with low confidence to be 1 row.  The
         estimated time for this step is 1 second.
      6) Finally, we send out an END TRANSACTION step to all AMPs involved
         in processing the request.
      -> No rows are returned to the user as the result of statement 1.
         The total estimated time is 23.16 seconds.

    Related Topics

  • “INSERT/INSERT … SELECT” on page 370
  • “MERGE” on page 426
  • “UPDATE (Positioned Form)” in SQL Stored Procedures and Embedded SQL
  • “UPDATE (Upsert Form)” on page 515
  • ANSI Temporal Table Support
  • Temporal Table Support