# SQL - UPDATE

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

## Syntax

UPDATE <class>|cluster:<cluster>|<recordID>
[UPSERT]
[RETURN <returning> [<returning-expression>]]
[WHERE <conditions>]
[LOCK default|record]
[LIMIT <max-records>] [TIMEOUT <timeout>]

Where:

• INCREMENT increments the field by the value. If the record had 10 as a 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
• REMOVE, removes an item in collection and maps fields
• PUT, puts an entry into map fields
• CONTENT, replaces the record content with a JSON
• MERGE, merges the record content with a JSON
• LOCK specifies how the record is locked between the load and the update. It can be a value between:
• DEFAULT, no lock. In case of concurrent update, the MVCC throws an exception
• RECORD, locks the record during the update
• UPSERT updates a record if it already exists, or inserts a new record if it does not, all in a single statement. This avoids the need to execute 2 commands, one for the query and a conditional insert/update. UPSERT requires a WHERE clause and a class target
• RETURN specifies what to return as <returning>. If <returning-expression> is specified (optional) and returning is BEFORE or AFTER, then the expression value is returned instead of record. <returning> can be a value between:
• COUNT, the default, returns the number of updated records
• BEFORE, returns the records before the update
• AFTER, returns the records after the update
• WHERE, SQL-Where condition to select records to update
• LIMIT, sets the maximum number of records to update
• TIMEOUT, if any limits the update operation to a timeout

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

To know more about conditions, take a 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 5: Put a map entry into a map

> UPDATE Account PUT addresses='Luca', #12:0

### 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 the first 20 records that satisfy a condition

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

### Example 9: Update a record or insert if it does not already exist

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

### Example 10: Update a web counter, avoiding concurrent accesses

UPDATE7:0 INCREMENT Counter = 123 RETURN BEFORE $current.Counter UPDATE7:0 SET gender='male' RETURN AFTER$current.exclude("really_big_field")