Using ARRAY Element Reference with a SET Clause

Teradata Vantageā„¢ Data Types and Literals

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1143-162K

You can use an ARRAY element reference as the target value in a SET clause of an UPDATE or MERGE statement to set the value of an individual element in an ARRAY.

If you set an element in the middle of a 1-D array to a specific value, and the elements previous to this one are not yet initialized, then these elements will be automatically initialized to null. For an n-D ARRAY type, if you set an element in the middle of the array to a specific value, and the elements previous to this one, across all dimensions in row-major order, are not yet initialized, then these elements will be automatically initialized to null.

Note that if the element type of the array is a variable-length type, and if the value passed to initialize the element is larger than the maximum size of the element type, Teradata Database automatically truncates the passed value to the maximum size of the element type. This truncation behavior occurs with transaction processing in Teradata session mode only. In ANSI session mode, Teradata Database does not truncate the passed value, and returns an error instead. This behavior is identical to that of distinct and structured UDTs.

In addition, existing implicit CAST functionality, such as casting from CHAR to Timestamp or Timestamp to DATE, is not supported for the ARRAY element reference. The source data type for the SET clause must be the same as the element data type of the array. When the target of the SET clause is an ARRAY element reference, you must use an explicit CAST for the source value of a SET clause that has a different data type than the element type of the array.