3. Delete from#

3.1. Synopsis#

DELETE FROM { <table_name> | <find_expression> } [, ... ]

  IN { RANGE <range_spec> | '[' RANGE <range_spec> [, ...] ']' }
    [ WITH DAYS IN ( <day>, <day> ) ]
    [ WITH TIME IN ( <time>, <time> ) ]

[ WHERE { <condition> | <duplicate_expression> } ]

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

find_expression ::=
  FIND ( { <tag_expression> | NOT <tag_expression> } [ AND ... ] )

tag_expression ::=
  TAG = 'tag_name'

duplicate_expression ::=
  DUPLICATE [ ( <column_name> [, ... ] ) ]

3.2. Description#

DELETE FROM removes rows from one or more tables in a specified range matching an (optionally provided) condition.

3.3. Parameters#

table_name: The name of the table to retrieve rows from.
find_expression: When your tables are tagged, you can use inline key/value lookups to perform your query over multiple tables. To match all tables that have the tag “stocks”, you can use FIND(tag='stocks' AND type=ts).
condition: A WHERE condition is any expression that evaluates to a boolean. Any row that does not match this predicate will be filtered from the results. For an overview of valid operators, please refer to the comparison operators reference.
duplicate_expression: A DUPLICATE expression describes the condition when two rows are considered duplicates. If a column filter is specified (DUPLICATE(col1, col2, ...)) only those columns are compared. The ‘$timestamp’ column must be included when specifying a column filter. If no column filter is specified (DUPLICATE), all columns are compared, including ‘$timestamp’. Any row being a duplicate of a previously seen row will be removed from the results. When multiple tables are specified, the operation is applied separately to each table. Rows from different tables are never considered duplicates. Columns in a column filter must be present in all given tables.
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.
day: A weekday. Can be one of mon, tue, wed, thu, fri, sat or sun. When defining a subrange using WITH DAYS IN, is both left and right inclusive: WITH DAYS IN (mon, tue) will match both Monday and Tuesday.
time: Time of day, having a precision of either minutes, seconds or nanoseconds. When defining a subrange using WITH TIME IN, is left inclusive and right exclusive: WITH TIME IN (09:22:00, 09:22:01) will not match a row whose time is exactly 09:22:01 AM.

3.4. Examples#

Delete everything from the year 2017 in table example:

DELETE FROM example in range(2017, 2018)

Delete everything before 2017 in table example:

DELETE FROM example in range(1970, 2017)

Delete all the data for the last hour whose column value ‘col’ is equal to 2:

DELETE FROM example in range(now, -1h) WHERE col=2

Delete all the duplicate rows for the last hour:

DELETE FROM example in range(now, -1h) WHERE DUPLICATE

Delete all the rows with duplicated timestamp and ‘col’ values for the last hour:

DELETE FROM example in range(now, -1h) WHERE DUPLICATE('$timestamp', col)

Delete everything from the year 2017 in all tables tagged with ‘nyse’:

DELETE FROM FIND(tag='nyse') in range(2017, 2018)

Delete everything from the last week in all tables tagged with ‘nyse’:

DELETE FROM FIND(tag='nyse') in range(now, -1w)