15.00 - ARRAY_UPDATE - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

ARRAY_UPDATE

Purpose  

Updates all or a subset of the elements in array_expr to the specified new value.

Syntax  

System function syntax:

Method-style syntax:

where:

 

Syntax element…

Specifies…

array_expr

an ARRAY expression, which is one of the following:

  • A reference to an ARRAY column
  • An ARRAY constructor expression. See “ARRAY Constructor Expression” on page 195.
  • A UDF expression
  • A UDM expression
  • new_value

    an expression that evaluates to a value with the same data type as the element type of array_expr.

    scope_reference

    an optional ARRAY scope reference. See “ARRAY Scope Reference” on page 198.

    array_index

    an optional index to the array, which is one of the following:

  • An integer value.
  • An array instance of the predefined array type ArrayVec with a comma-separated list of integer values to define the bounds. For details, see “ARRAY Scope Reference” on page 198.
  • The value of array_index must be within the limits of array_expr.

    ANSI Compliance

    This is a Teradata extension to the ANSI SQL:2011 standard.

    Usage Notes  

    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.
  • Result Type and Value

    ARRAY_UPDATE returns a new copy of the array specified by array_expr with the specified elements updated to the new value.

    Example  

    Consider the following one-dimensional ARRAY data type and table.

    CREATE TYPE phonenumbers AS CHAR(10) ARRAY[20];
     
    CREATE TABLE employee_info (eno INTEGER, phonelist phonenumbers);

    The following query returns an updated copy of the phonelist array, with all the elements updated to the new value:

    SELECT ARRAY_UPDATE(phonelist, '9095551234')
    FROM employee_info;

    The following query returns an updated copy of the phonelist array, with a subset of the elements updated to the new value, as specified by the scope reference. The result is that elements 2, 3, and 4 are updated to the new value. The rest of the elements in the array retain their original values.

    SELECT ARRAY_UPDATE(phonelist, '9095551234', 2, 4)
    FROM employee_info;

    The following is the same query using method-style syntax.

    SELECT phonelist.ARRAY_UPDATE('9095551234', 2, 4)
    FROM employee_info;

    Example  

    Consider the following 2-D ARRAY data type and table.

    CREATE TYPE shot_ary AS INTEGER ARRAY[1:50][1:50];
     
    CREATE TABLE seismic_data (
       id INTEGER,
       shot1 shot_ary,
       shot2 shot_ary);

    The following query returns an updated copy of the shot1 array, with all the elements updated to the new value.

    SELECT ARRAY_UPDATE(shot1, 0)
    FROM seismic_data;

    The following query returns an updated copy of the shot1 array, with a subset of the elements updated to the new value, as specified by the scope reference. The result is that the elements in the scope reference range [5:10][5:10] are updated to the new value. The rest of the elements in the array retain their original values.

    SELECT ARRAY_UPDATE(shot1, 0, NEW arrayVec(5,5), NEW arrayVec(10,10))
    FROM seismic_data;