SQLUpdate

SQL Update

Update one or more records into the current database. Remember that Orient can work also in schema-less mode, so you can create any field at-the-fly. Furthermore OrientDB works on collection. This is the reason why the SQL has some extension to handle collections.

Syntax

UPDATE <class>|cluster:<cluster>> SET|INCREMENT [= <field-value>](<field-name>)[<field-name> = <field-value>](,)* [<conditions>] (WHERE) [<max-records>](LIMIT)

Where:

• SET updates the field
• INCREMENT increments the field by the value. If the record had 10 as value and "INCREMENT value = 3" is executed, then the new value will be 13. This is useful for atomic updates of counters. Use negative numbers to decrement.

For COLLECTIONS:

UPDATE <class>|cluster:<cluster>> [[<field-name> = <field-value>](ADD|REMOVE])[<field-name> = <field-value>](,)* [<conditions>](WHERE)

For MAPS:

UPDATE <class>|cluster:<cluster>> [[<field-name> = <map-key> [,<map-value>]](PUT|REMOVE])[<field-name> = <map-key> [,<map-value>]](,)* [<conditions>](WHERE)

Note that RecordID must be prefixed with '#'. Example: #12:3.

To know more about conditions look at WHERE conditions.

Examples

Example 1: change the value of a field

> update Profile set nick = 'Luca' where nick is null

Updated 2 record(s) in 0,008000 sec(s).

Example 2: remove a field from all the records

> update Profile remove nick

Example 3: Add a value into a collection

> update Account add addresses = #12:0

Example 4: Remove a value from a collection

> update Account remove addresses = #12:0

Example 5: Put a map entry into a map

> update Account put addresses = 'Luca', #12:0

Example 6: Remove a value from a map

> update Account remove addresses = 'Luca'

Example 7: Update an embedded document

Update command can take a JSON as value to update:

> update Account set address = { "street" : "Melrose Avenue", "city" : { "name" : "Beverly Hills" } }

Example 8: update only the first 20 records that satisfy a condition

> update Profile set nick = 'Luca' where nick is null limit 20

To know more about the SQL syntax used in Orient take a look to: SQL Query.