SQL - `UPDATE`

Update one or more records in the current database. Remember: ArcadeDB can work in schema-less mode, so you can create any field on-the-fly. Furthermore, the command also supports extensions to work on collections.

Syntax:

UPDATE <type>|BUCKET:<bucket>|<recordID>
  [SET|REMOVE <field-name> = <field-value>[,]*]|[CONTENT|MERGE <JSON>]
  [UPSERT]
  [APPLY DEFAULTS]
  [RETURN <returning> [<returning-expression>]]
  [WHERE <conditions>]
  [LIMIT <max-records>] [BATCH <batch-size>] [TIMEOUT <MilliSeconds>]

SET Defines the fields to update.
REMOVE Removes an item in collection and map fields or a property.
CONTENT Replaces the record content with a JSON document.
MERGE Merges the record content with a JSON document.
UPSERT Updates a record if it exists or inserts a new record if it doesn’t. This avoids the need to execute two commands, (one for each condition, inserting and updating). UPSERT requires a WHERE clause and a type target. There are further limitations on UPSERT, explained below. Practically UPSERT means: UPDATE if the WHERE condition is fulfilled, otherwise INSERT.
APPLY DEFAULTS applies a properties' default attribute on an update again.
RETURN Specifies an expression to return instead of the record and what to do with the result-set returned by the expression. The available return operators are:
- COUNT Returns the number of updated records. This is the default return operator.
- BEFORE Returns the records before the update.
- AFTER Return the records after the update.
WHERE Defines the subset of records to be updated.
LIMIT Defines the maximum number of records to update.
BATCH Defines the number of records to update in each transaction batch. When set, the command commits the current transaction every <batch-size> records and begins a new one. This is essential for updating large datasets (e.g., millions of records) that would otherwise consume too much memory in a single transaction.
TIMEOUT Defines the time you want to allow the update run before it times out.

Examples

Update to change the value of a field:

ArcadeDB> UPDATE Profile SET nick = 'Luca' WHERE nick IS NULL

Update to remove a field from all records:

ArcadeDB> UPDATE Profile REMOVE nick

Update to remove a value from a collection, if you know the exact value that you want to remove:

Remove an element from a link list or set:

ArcadeDB> UPDATE Account REMOVE address = #12:0

Remove an element from a list or set of strings:

ArcadeDB> UPDATE Account REMOVE addresses = 'Foo'

Append an element to a list or set of strings:

ArcadeDB> UPDATE Account SET addresses += 'Foo'

Update to remove a value, filtering on value attributes.

Remove addresses based in the city of Rome:

ArcadeDB> UPDATE Account REMOVE addresses = addresses[city = 'Rome']

Update to remove a value, filtering based on position in the collection.

ArcadeDB> UPDATE Account REMOVE addresses = addresses[1]

This remove the second element from a list, (position numbers start from 0, so addresses[1] is the second element).

Update a map entry

ArcadeDB> UPDATE #87:0 SET pages += { "homePage": "doctor.html" }

Update to remove a value from a map

ArcadeDB> UPDATE Account REMOVE addresses = 'Luca'

Update to remove a property values from records

ArcadeDB> UPDATE Account REMOVE addresses WHERE addresses = 'unknown'

Update an embedded document. The UPDATE command can take JSON as a value to update.

ArcadeDB> UPDATE Account SET address={ "street": "Melrose Avenue", "city": {
            "name": "Beverly Hills" } }

Update the first twenty records that satisfy a condition:

ArcadeDB> UPDATE Profile SET nick = 'Luca' WHERE nick IS NULL LIMIT 20

Update millions of records in batches of 10,000 to avoid loading all records into a single transaction:

ArcadeDB> UPDATE Profile SET verified = true WHERE verified IS NULL BATCH 10000

Update a record or insert if it doesn’t already exist:

ArcadeDB> UPDATE Profile SET nick = 'Luca' UPSERT WHERE nick = 'Luca'

Updates using the RETURN keyword:

ArcadeDB> UPDATE ♯7:0 SET gender='male' RETURN AFTER @rid
ArcadeDB> UPDATE ♯7:0 SET gender='male' RETURN AFTER @this
ArcadeDB> UPDATE ♯7:0 SET gender='male' RETURN AFTER $current.exclude("really_big_field")

In the event that a single field is returned, ArcadeDB wraps the result-set in a record storing the value in the field result. This avoids introducing a new serialization, as there is no primitive values collection serialization in the binary protocol. Additionally, it provides useful fields like version and rid from the original record in corresponding fields. The new syntax allows for optimization of client-server network traffic.

For more information on SQL syntax, see SELECT.

Limitations of the UPSERT Clause

The UPSERT clause only guarantees atomicity when you use a UNIQUE index and perform the look-up on the index through the WHERE condition.

ArcadeDB> UPDATE Client SET id = 23 UPSERT WHERE id = 23

Here, you must have a unique index on Client.id to guarantee uniqueness on concurrent operations.

SQL - UPDATE

SQL - `UPDATE`