Updates

You'll be presented the SQL UPDATE and UPSERT syntax.


Update Syntax

You can execute updates through SQL using the /sql endpoint or through any tool that supports SQL and can connect to SQL drivers. Below you can check the UPDATE SQL syntax.

UPDATE <table_name> SET { <column_reference> = <expression> } [ , ... ] WHERE { entity-id = <expression>  } [ { AND | OR } ... ]

Upsert syntax

You can execute updates through SQL using the UPSERT command. This command will update an existing record or insert a new record if an existing record is not identified. Below you can check the UPSERT SQL syntax.

UPSERT INTO <table_name>
( <column_reference> [ , ... ] )
VALUES
( { <expression> | NULL } [ , ... ] )

Update/Upsert Limits

Below is the list of limits and restrictions for updating or upserting records using SQL.

Operation Limits
Column Name Reference For reference a single name column, just insert in the parameters of the dimension. If a column has a hyphen or is a broken event column, you need to insert brackets around them. Example: [entity-id], [eventcol.0.value].
Boolean Values Must be True or False
Update on Event Columns Insertion on event columns insertions can be made in two ways:
  • Using a raw JSON aggregate value. Example:
    UPDATE sales SET city = 'Chicago', eventcol = '{\"value\": 1, \"date\": \"2017-01-01T00:00:00-05:00\"}' WHERE [entity-id] = 'ram';
  • Broke up into individual columns using the format [eventcolumn.index.date/value]. Example:
    UPDATE sales SET city = 'Chicago', [eventcol.0.value] = 1, [eventcol.0.date] = '01/01/2017' WHERE [entity-id] = 'ram';
String Values Values for unique-id, string and string-event columns can have at most 1,000 characters and are only stored in lowercase and need to be inside quotation marks.
Integer Values Values for integer and integer-event columns must be in the interval from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807.
Decimal Values Values for decimal and decimal-event columns must be in the interval from (-263/10p) to ((263 - 1)/10p) given p decimal place and cannot have more than 20 decimal places.
Decimal separators are done with dots. Ex: 90.923
Date Values Values for date column must be in YYYY-MM-DD format and need to be inside quotation marks.
Datetime Values Values for datetime column must be in YYYY-MM-DDTHH:MM:SSZ format and need to be inside quotation marks.
Event Values Dates in string-event, integer-event, decimal-event and geolocation-event cannot be older than 1990-01-01.
Eventual Consistency The inserted values will be ready to be queried up to 5 seconds after insertion.