UPDATE Statement

You can use an update statement to update a row in a table. It also supports updating multiple rows in a table that share the same shard key. You can find which columns in your table comprise of the shard key here, if you are running an On-Premises version of Oracle NoSQL.

Syntax

update_statement ::=
   UPDATE table_name [[AS] table_alias]
   update_clause ("," update_clause)*
   WHERE expression
   [returning_clause]

returning_clause ::= RETURNING select_list

For definitions of syntax components referenced in this statement, see:

• table_name
• table_alias
• update_clause
• expression
• 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, you specify the table to update 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. The current implementation supports both single-row and multiple-row updates

  • To update a single row, the WHERE clause must specify the complete primary key.

  • To update multiple rows, the WHERE clause must specify the shard key, ensuring that all rows reside on the same shard. This enables Oracle NoSQL Database to perform this update in an ACID transaction. For examples, see Example: Updating multiple rows.

  A single update transaction applies all changes if successful or makes no change if an error occurs.

• There is an optional RETURNING clause. The RETURNING clause works only when the WHERE clause includes the complete primary key. Currently, it only supports single-row updates.

  • If the clause is not present, the result of the update statement is the number of rows updated. For example,

    • Zero is returned if there is 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 row selected by the WHERE clause.

    • One is returned if there is a single row satisfying the conditions in the WHERE clause, and so on.

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

• There is also a limit on the number of records a single update query can update. By default, the system sets this limit to 1,000. If an update statement tries to modify more than 1,000 records in a single transaction, it will fail during the process. To avoid this, use setLimit(int limit) method, to override the default limit value. For more information, see QueryRequest.setLimit(int limit). By setting the desired limit, you can work with large update requirements.

Note: While you can override the default limit, we do not recommend doing so for a large number of rows, as this can cause the update operation or the other read and write operations that affect the same data during the transaction to time out or experience higher latency.

The UPDATE statement is used to update the rows in the table. If you want to supply user-supplied metadata for the update operation, you can use the available QueryRequest API. For an example, see Using User-Supplied Metadata in Write Operations from Developers Guide.