DELETE Statement

The DELETE statement is used to remove from a table a set of rows satisfying a condition.

Syntax

delete_statement ::= 
   [variable_declaration]
   DELETE FROM table_name [[AS] table_alias] 
   WHERE expression 
   [returning_clause]

returning_clause ::= RETURNING select_list

Semantics

The delete statement is used to delete from a table a set of rows satisfying a condition. The condition is specified in a WHERE clause that behaves the same way as in the SELECT expression. The result of the DELETE statement depends on whether a RETURNING clause is present or not. Without a RETURNING clause the DELETE returns the number of rows deleted. Otherwise, for each deleted row the expressions following the RETURNING clause are computed the same way as in the SELECT clause and the result is returned to the application. Finally, the DELETE statement may start with declarations of external variables used in the rest of the statement. As in queries, such declarations are mandatory.

If any error occurs during the execution of a DELETE statement, there is a possibility that some rows will be deleted and some not. The system does not keep track of what rows got deleted and what rows are not yet deleted. This is because Oracle NoSQL Database focuses on low latency operations. Long running operations across shards are not coordinating using two-phase commit and lock mechanism. In such cases, it is recommended that the application re-run the DELETE statement.

Example 7-4 Deleting Rows with SQL

The following statement deletes all users whose age is less than 16, returning the first and last name of each deleted user.

DELETE FROM users
WHERE age < 16
RETURNING firstName, lastName;