SQL - UPDATE

Update one or more records in the current database. Remember: OrientDB 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 <class>|CLUSTER:<cluster>|<recordID>
  [SET|REMOVE <field-name> = <field-value>[,]*]|[CONTENT|MERGE <JSON>]
  [UPSERT]
  [RETURN <returning> [<returning-expression>]]
  [WHERE <conditions>]
  [LOCK default|record]
  [LIMIT <max-records>] [TIMEOUT <timeout>]

• SET Defines the fields to update.
• REMOVE Removes an item in collection and map fields.
• CONTENT Replaces the record content with a JSON document.
• MERGE Merges the record content with a JSON document.
• LOCK Specifies how to lock the record between the load and update. You can use one of the following lock strategies:
  • DEFAULT No lock. Use in the event of concurrent updates, the MVCC throws an exception.
  • RECORD Locks the record during the update.

• 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 class target. There are further limitations on UPSERT, explained below.

• 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
• LIMIT Defines the maximum number of records to update.
• TIMEOUT Defines the time you want to allow the update run before it times out.

NOTE: The Record ID must have a # prefix. For instance, #12:3.

Examples:

• Update to change the value of a field:

  orientdb> UPDATE Profile SET nick = 'Luca' WHERE nick IS NULL
  
  Updated 2 record(s) in 0.008000 sec(s).
  

• Update to remove a field from all records:

  orientdb> 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:

  orientdb> UPDATE Account REMOVE address = #12:0
  

  Remove an element from a list or set of strings:

  orientdb> UPDATE Account REMOVE addresses = 'Foo'
  

• Update to remove a value, filtering on value attributes.

  Remove addresses based in the city of Rome:

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

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

  orientdb> 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 elelment).

• Update to remove a value from a map

  orientdb> UPDATE Account REMOVE addresses = 'Luca'
  

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

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

• Update the first twenty records that satisfy a condition:

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

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

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

• Updates using the RETURN keyword:

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

In the event that a single field is returned, OrientDB 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.

orientdb> 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.