1. SQL Reference Guide
  2. Data Row Management
  3. UPDATE Statement

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 the WHERE 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 in WHERE clause, or if the updates specified by the update clauses turned out to be no-ops for the single row selected by the WHERE clause. Otherwise, if there is a RETURNING clause, it acts the same way as the SELECT 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 the WHERE conditions, the update statement returns an empty result.