UPDATE

Change fields on the records you choose, and leave every other field as it was.

Syntax

Give the fields to change as a JSON object, then the table, then a FILTER.

UPDATE {"column": value, …} IN table FILTER(condition, …)
UPDATE EDGE {"key": value, …} IN name_edge FILTER(condition, …)

Parameters and arguments

  • {…}

    The fields to change: column names and new values. Every name must be a column of the table. It cannot be empty

    Required: Yes
  • value

    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

    Required: Yes
  • table

    The table to change, written after IN. It must be a table your account may write

    Required: Yes
  • FILTER(…)

    Chooses the records to change. It takes every condition FILTER takes, including IN [ … ], BETWEEN, NOT and OR. WHERE(…) means the same

    Required: Yes

For an edge, the object holds metadata keys, which are merged into the edge's existing metadata, and the FILTER chooses the edges. A FILTER that matches no edge is not an error: the answer carries the notice "UPDATE EDGE matched 0 rows".

Examples

Set a new quantity for one stock record.

Return type and behaviour

UPDATE answers that it succeeded, with the number of records changed in its modifiedCount and writesExecuted counts.

A FILTER that matches no record is an error, not a quiet success: you are told that nothing was updated, so a mistyped condition does not look like it worked.

Only the fields you name change. Every other field keeps its value.

Errors and exceptions
ErrorWhat happenedHow to recover
HTQL-2011 UPDATE requires a FILTER clauseThe statement has no FILTERAdd FILTER(condition)
HTQL-2001 ROW_NOT_FOUNDNo record matches the FILTER, so nothing was updatedCheck the condition, and check the record is in your tenant
HTQL-1002The object is empty, or the table is missingWrite UPDATE {"column": value} IN table FILTER(…)
HTQL-3010 Table is read-onlyThe table is a platform table or is not one your account may writeUpdate a table your account may write
HTQL-4014A name in the object is not a column of the tableCheck the column's name
HTQL-6012An UPDATE EDGE names _from or _toRemove the edge and insert a new one
Placement and context

UPDATE is a statement of its own. It can also appear in the body of a FOR loop. A LET before it can hold a value the object and the FILTER share.

Limitations
  • Not supported: an UPDATE without a FILTER. It is refused.
  • Not supported: moving an edge's ends with UPDATE EDGE. _from and _to are refused, because they are the edge's identity. To relink, remove the edge and insert a new one.
  • The integer numId column cannot be changed.
  • Write the database's own column names, both in the object and in the FILTER.
Permissions and security

UPDATE 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
  • Check a condition with LOOKUP before you use it in an UPDATE.
  • Filter on a unique column, such as an identifier, to change one record.
  • Change only the fields that need it.