15.00 - ARRAY_UPDATE_STRIDE - Teradata Database

ARRAY_UPDATE_STRIDE

Purpose  

Updates all or a subset of the elements in array_expr to the specified new value. The stride argument indicates how many elements should be skipped between each updated element.

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.
stride	an unsigned integer value indicating the number of elements that should be skipped between each updated element.
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_STRIDE 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 stride argument indicates how many elements should be skipped between each updated element.

The following rules apply to the ARRAY_UPDATE_STRIDE 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. In this case, stride_value has no effect.

The stride_value argument specifies the number of elements to skip between each element to be updated. For example, if stride_value is 1, then every other element within the affected range of elements is updated. The affected range of elements depends on whether or not scope_reference is specified. For a multidimensional array, the elements are stored in row-major order so they are traversed and skipped according to stride_value in this order.

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

If an element is skipped that was previously uninitialized, it is updated to NULL.

Result Type and Value

ARRAY_UPDATE_STRIDE 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 every other element updated to the new value:

SELECT ARRAY_UPDATE_STRIDE(phonelist, '9095551234', 1)

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 and stride value. The result is that elements 2 and 4 are updated to the new value. The rest of the elements in the array retain their original values.

SELECT ARRAY_UPDATE_STRIDE(phonelist, '9095551234', 1, 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 every other element updated to the new value.

SELECT ARRAY_UPDATE_STRIDE(shot1, 0, 1)

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 and stride value. The result is that every 5th element beginning with the first element in the scope reference range [1:50][1:50] is updated to the new value. The rest of the elements in the array retain their original values.

SELECT ARRAY_UPDATE_STRIDE(shot1, 0, 5, NEW arrayVec(1,1), 

   NEW arrayVec(50,50))

FROM seismic_data;

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

SELECT shot1.ARRAY_UPDATE_STRIDE (0, 5, NEW arrayVec(1,1), 

   NEW arrayVec(50,50))

FROM seismic_data;