Update Clauses

SET Clause
ADD Clause
PUT Clause
REMOVE Clause
SET TTL Clause

Update clauses are used to describe what modifications are to be made to a table row. There are five types of clauses that you can use:

Multiple clauses and a combination of clauses can be used by seperating it by comma. For example:

update_clause :
(SET set_clause (COMMA (update_clause | set_clause))*) |
(ADD add_clause (COMMA (update_clause | add_clause))*) |
(PUT put_clause (COMMA (update_clause | put_clause))*) |
(REMOVE remove_clause (COMMA remove_clause)*) ;

SET Clause

set <target_expr> = <new-value_expr>

The SET clause changes the value of existing information in the targeted table. Its target expression which identifies the information to change. Its new-value expression identifies what the targeted information will become.

  • Target Expression

    A target expression can be atomic or complex, but it will always be nested inside a complex item (its parent item). For each such target expression, the new-value expression is evaluated and the new-value results are used to replace the target item.

    If the target expression returns a NULL item, then either the target item itself is the NULL item or one of its ancestors is NULL. If the target item is NULL, the result of the new-value expression are used to replace the NULL. If one of the target item's ancestors are NULL, then the entire clause is a noop.

  • New-Value Expression

    The new-value expression can return zero or more items. If it returns an empty result, the SET is a noop. If it returns more than one item, the items are enclosed inside a newly constructed array in the same was as the way the SELECT clause treats multi-valued expressions in the select list. The result of the new-value expression is then cast to the type expected by the parent item for the target field. (See Cast Expressions for details.) If the cast fails, an error is raised; otherwise the new item replaces the target item within the parent item.

    The new-value expression may reference the implicitly declared variable $, which is bound to the current target item. Use of the $ variable makes it possible to have target expressions that return more than one item. In this case the SET clause will iterate over the set of target items, bind the $ variable for each target item, compute the new-value expression, and replace the target item with the result of the new-value expression.

For examples of using a SET clause, see Changing Field Values, Changing an Existing Element in an Array, and Updating Existing Map Elements

ADD Clause

add <target_expr> <position_expression> <new-elements_expr>

or

add <target_expr> <new-elements_expr>

The ADD clause is used to add new elements into one or more arrays. It consists of:

  • a target expression, which should return one or more array items,

  • an optional position expression, which specifies the position within the array where the new element(s) should be placed at,

  • and a new-elements expression that returns the new elements to insert.

This clause iterates over the sequence returned by the target expression. For each target item, if the item is not an array it is skipped. Otherwise, the position expression (if present) and the new-elements expression are computed for the current target array. These two expressions may reference the $ variable, which is bound to the current target array.

If the position expression is missing, or if it returns an empty result, the new elements are appended at the end of the target array. An error is raised if the position expression returns more than one item or a non-numeric item. Otherwise, the returned item is cast to an integer. If this integer is less than 0, it is set to 0. If it is greater or equal to the array size, the new elements are appended.

If the the new-values expression returns nothing, the ADD clause is a noop. Otherwise, each item returned by this expression is cast to element type of the array. An error is raised if any of these casts fails. Otherwise, the new elements are inserted into the target array at the indicated position.

For examples of using an ADD clause, see Adding Elements to an Array.

PUT Clause

put <target_expression> <new-fields_expression>

The PUT clause is used to add new fields into one or more maps. It consists of:

  • a target expression which should return one or more map items,

  • and a new-fields expression that returns one or more maps or records. These are the fields that are inserted in the target maps.

The PUT clause iterates over the sequence returned by the target expression. For each target item, if the item is not a map it is skipped. Otherwise, the new-fields expression is computed for the current target map. The new-maps expression may reference the $ variable, which is bound to the current target map.

If the the new-fields expression returns nothing, the PUT is a noop. Otherwise, for each item returned by the new-fields expression, if the item is not a map or a record, it is skipped. If the item is a map or record, the fields of the map/record are “merged” into the current target map. This merge operation will insert a new field into the target map if the target map does not already have a field with the same key. Otherwise it will set the value of the target field to the value of the new field.

For examples of using a PUT clause, see Adding Elements to a Map.

REMOVE Clause

remove <target_expression>

The remove clause consists of a single target expression, which computes the items to be removed. The REMOVE clause iterates over the target items, and for each item:

  • If its parent is a record, an error is raised.

  • If the target item is not NULL, it is removed from its parent.

  • If the target item is NULL, it is skipped.

    Note that if the target item is NULL, then one of its ancestors must be NULL. This is because arrays and maps cannot contain NULLs. Consequently, the target item is skipped because of the NULL ancestor.

For examples of using the REMOVE clause, see Removing Elements from Arrays and Removing Elements from a Map.

SET TTL Clause

set TTL <add_expression> HOURS|DAYS 

or

set TTL USING TABLE DEFAULT

Every table row has an expiration time that is specified in terms of a Time to Live (TTL) value. TTL values are specified as a number of days or hours. If zero (0), the row will never expire. If a row expires, its data can no longer appear in query results.

The expiration time for a row is always computed when the row is first inserted into a table. However, you can use the SET TTL clause to specify a new expiration time. The value you specify for this clause is used to compute the new expiration time.

The SET TTL clause comes in two flavors. The first contains an expression which computes a new TTL value as follows:

  1. If the result of this expression is empty, the SET TTL clause is a noop.

  2. If the expression result is not empty, it is cast to an integer.

  3. If the resulting integer is negative, it is set to 0. This means the table row will never expire.

  4. If the resulting integer is non-negative, it must be followed by a unit designation of either HOURS or DAYS.

  5. The new expiration time is computed based on the current time (in UTC) plus the number of hours/days computed in <add_expression> rounded up to the next full hour/day. That is, if the current time is 2017-06-01T10:05:30.0 and the TTL value evaluates to 3 hours, the expiration time will be 2017-06-01T14:00:00.0.

For an example of using the SET TTL clause, see Managing Time to Live Values.