23. UPDATE#

23.1. Synopsis#

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

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

23.2. Description#

UPDATE changes rows in one or more existing tables. Use IN RANGE to limit the operation to one or more time ranges. Use WHERE to update only rows that match a condition. If you omit both clauses, QuasarDB updates all rows in the selected tables.

23.3. Parameters#

table_name: The name of a table to change.
column_name: The name of a column to change.
value: The new value for the column. The value type must be compatible with the column type. Use NULL to remove the current value.
expression: An expression that calculates the new value. The expression can use the current value of a column.
qualified_column_name: A column name that can include its table qualifier. Use a qualifier when the same column name exists in more than one target table.
condition: A Boolean expression that selects the rows to change. For more information, refer to comparison operators.
timestamp: An absolute timestamp. Use a date or a date and time. You can use day, second, or nanosecond precision. For more information, refer to timestamps.
time_offset: A relative time offset. Use it with an absolute timestamp. For more information, refer to time offsets.

23.4. Examples#

Update all values in a range:

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

Update all values that match a condition:

UPDATE example SET my_double = 12.34 WHERE my_double < 0

Update all values in a range that match a condition:

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)

Increase a value by 10 percent:

UPDATE example SET price = price * 1.10 WHERE price > 0

Update all tables that have the nyse tag:

UPDATE FIND(TAG='nyse') SET status = 'closed'