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 tableParameters 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: YesINSERT {…}The whole record to add when none exists. A key you do not name is not set
Required: YesUPDATE {…}The fields to change when the record exists
Required: YesIN tableThe 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
| Error | What happened | How to recover |
|---|---|---|
HTQL-2011 | A match column is not set in the INSERT object | Add the column to the INSERT object |
HTQL-1002 | The match, INSERT or UPDATE object is missing or empty, or the table is missing | Write all three parts and the table |
HTQL-3010 Table is read-only | The table is a platform table or is not one your account may write | Use 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
numIdcolumn 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_idout. HTQLDB adds yours.