19. Update

19.1. Synopsis

 UPDATE <table_name>
    SET <column_name> = <value> [, ... ]
  [ IN { RANGE <range_spec> | '[' RANGE <range_spec> [, ...] ']' }
  [ WHERE <condition> ]

range_spec ::=
  ( <timestamp>, <timestamp> )
  ( <timestamp>, <time_offset> )

19.2. Description

UPDATE will modify rows of an existing table to a new value. The affected rows are those in the given timestamp range and matching the WHERE criteria.

19.3. Parameters

table_name

The name of the table which should be modified.

column_name

The name of the column which should be modified.

value

The value to set in the respective column. The format of the valuedata needs to match the type of the column, otherwise an error will be thrown. A special value NULL can be used to set data to a NULL-value (missing value).

condition

A WHERE condition is any expression that evaluates to a boolean. Any row that matches this predicate will be updated. For an overview of valid operators, please refer to the comparison operators reference.

timestamp

An absolute timestamp. This can be either a date or a date + time. Supports precision for days, seconds or nanoseconds. For more information, please refer to the documentation for timestamps.

time_offset

A relative offset, can only be used in combination with an absolute_timestamp. For more information, please refer to the documentation for timestamps.

19.4. Examples

Update all values in a range:

UPDATE example SET my_int = 1234 IN RANGE (now, -1d)

Update all values matching given criteria:

UPDATE example SET my_double = 12.34 WHERE my_double < 0

Update all values in a range matching given criteria:

UPDATE example SET my_blob = ‘fsesdf’ IN RANGE (2018, +1mon) WHERE my_double < 0

Update multiple columns:

UPDATE example SET my_int = 1234, my_double = 12.34 IN RANGE (now, -1d)

Update all values to a NULL-value:

UPDATE example SET my_int = NULL IN RANGE (now, -1d)

Update a timestamp column:

UPDATE example SET my_ts = 2018-08-01T03:00:00 IN RANGE (now, -1d)