UPSERT

Write a record whether or not it exists yet.

Syntax

Give the match, the record to add, and the fields to change, then the table.

UPSERT {"key": value}
  INSERT {"key": value, "column": value, …}
  UPDATE {"column": value, …}
  IN table

Parameters and arguments

  • match {…}

    The column or columns that identify the record. Their names are the key; each must also appear in the INSERT object

    Required: Yes
  • INSERT {…}

    The whole record to add when none exists. A key you do not name is not set

    Required: Yes
  • UPDATE {…}

    The fields to change when the record exists

    Required: Yes
  • IN table

    The table. It must be a table your account may write

    Required: Yes

A value is a literal, a list, a nested object, a variable, a function's result such as TODAY() or NOW(), a default such as x ?? "none", or, inside a FOR loop, a column of the current row such as o.total. The match, INSERT and UPDATE objects take values in the same way.

Examples

Create the record on the first run, and update its quantity on every run after.

Return type and behaviour

UPSERT answers that it succeeded. Its modifiedCount is the number of existing records it changed, and its writesExecuted is the number it changed plus the number it added.

Existing records are matched on the values in the INSERT object for the match columns. The statement updates those records first, and adds the new record only when none was updated.

As in INSERT, the caller's tenant is added to a new record, and a generated identifier is issued when the table has one.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-2011A match column is not set in the INSERT objectAdd the column to the INSERT object
HTQL-1002The match, INSERT or UPDATE object is missing or empty, or the table is missingWrite all three parts and the table
HTQL-3010 Table is read-onlyThe table is a platform table or is not one your account may writeUse a table your account may write
Placement and context

UPSERT is a statement of its own. It can also appear in the body of a FOR loop, which is how a sync updates each record it receives.

Limitations
  • Every column named in the match object must also be set in the INSERT object.
  • All three parts are required: the match, INSERT and UPDATE.
  • UPSERT works on one record each time. To write many, loop with FOR.
  • The integer numId column cannot be written.
Permissions and security

UPSERT runs as the caller, confined by row-level security to the caller's own account. A platform table such as orders refuses a direct write with HTQL-3010: its records change through the statement that owns them, such as INSERT ORDER {…}. A content table is written by a caller who holds the content area, and a link collection by the account that owns it.

A column classified above the caller's clearance cannot be written by that caller. The statement is refused, never partly applied.

Best practices
  • Use UPSERT where a statement may run more than once, such as a retry or a sync.
  • Put the fields that never change in INSERT, and only the fields that may change in UPDATE.
  • Leave tenant_id out. HTQLDB adds yours.