16.20 - Usage Notes - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ Data Types and Literals

Teradata Database
Teradata Vantage NewSQL Engine
March 2019
Programming Reference

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.