UPDATE Statement
An update statement can be used to update a row in a table.
Syntax
update_statement ::=
UPDATE table_name
[[AS] table_alias
]
update_clause
("," update_clause)*
WHERE expression
[returning_clause]
returning_clause ::= RETURNING select_list
Semantics
The update takes place at the server, eliminating the read-modify-write cycle, that is, the need to fetch the whole row at the client, compute new values for the targeted fields (potentially based on their current values) and then send the whole row back to the server.
Both syntactically and semantically, the update statement of the Oracle NoSQL Database is similar to the update statement of standard SQL, but with extensions to handle the richer data model of the Oracle NoSQL Database. So, as shown by the syntax above:
- First, the table to be updated is specified by its name and an optional table alias (the alias may be omitted only if top-level columns only are to be accessed; otherwise, as in read-only queries, the alias is required as the first step of path expressions that access nested fields).
- Then come one or more update clauses.
- The
WHERE
clause specifies what rows to update. In the current implementation, only single-row updates are allowed, so theWHERE
clause must specify a complete primary key. - Finally, there is an optional
RETURNING
clause. If not present, the result of the update statement is the number of rows updated. In the current implementation, this number will be 1 or 0. Zero will be returned if there was no row satisfying the conditions inWHERE
clause, or if the updates specified by the update clauses turned out to be no-ops for the single row selected by theWHERE
clause. Otherwise, if there is aRETURNING
clause, it acts the same way as theSELECT
clause: it can be a "*", in which case, the full updated row will be returned, or it can have a list of expressions specifying what needs to be returned. Furthermore, if no row satisfies theWHERE
conditions, the update statement returns an empty result.