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.