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. UPSERT works on document types and on vertex types: when the target is a vertex type and no existing record matches, a brand new vertex (not a plain document) is created, with all the graph capabilities (incoming/outgoing edges, @in/@out projections, etc.). For edge types, UPSERT is not available because edges always require FROM and TO endpoints; use CREATE EDGE … IF NOT EXISTS instead.
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'

Upsert a vertex by a natural key. With a UNIQUE index on Person.email, this either updates the existing person or creates a new vertex (not a plain document) of type Person:

ArcadeDB> UPDATE Person SET name = 'Luca', email = '[email protected]'
            UPSERT WHERE email = '[email protected]'

The newly created record is a real vertex, so it can immediately participate in graph traversals and be the source/target of CREATE EDGE.

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. This applies to both document and vertex types.

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.

The same rule applies when upserting vertices: define a UNIQUE index on the natural key first.

ArcadeDB> CREATE INDEX ON Person (email) UNIQUE
ArcadeDB> UPDATE Person SET name = 'Luca', email = '[email protected]'
            UPSERT WHERE email = '[email protected]'

Additional restrictions:

the WHERE condition cannot use OR. Only an AND chain of equality conditions is supported, because each equality term is also reused as the initial value of the property when a new record has to be created.
UPSERT is not available on edge types. To create an edge only if it does not already exist, use CREATE EDGE … IF NOT EXISTS.

SQL - UPDATE

SQL - `UPDATE`