Usage Notes | ARRAY_UPDATE | Teradata Vantage - 17.10 - Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - Data Types and Literals

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1143-171K
Language
English (United States)

ARRAY_UPDATE takes an array expression as an argument and updates all or a subset of the elements in array_expr with the value specified by new_value.

The following rules apply to the ARRAY_UPDATE function:
  • If array_expr is NULL, then the result value is NULL.
  • The value of new_value must evaluate to the same data type as the element type of array_expr, or it must be a data type that can be implicitly converted to the ARRAY element type.
  • If you do not specify scope_reference or array_index, then all the elements in array_expr are updated to the value of new_value.
  • If you specify scope_reference, then only the elements within the scope reference of array_expr are updated to the value of new_value. scope_reference must refer to consecutive elements in the array.
  • If you specify array_index, then only the single element specified by array_index is updated to the value of new_value in array_expr.
  • If an element to be updated contains a NULL, the NULL is replaced with the value of new_value in array_expr.
  • When one or more elements are modified, and there are uninitialized elements in the ARRAY value prior to the elements to be updated, then ARRAY_UPDATE sets any preceding uninitialized elements to NULL. This behavior is the same as that of a regular SQL UPDATE statement that sets an ARRAY element value.