Previous Next JavaScript must be enabled to correctly display this content

## 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-24 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

Example 7-25 Delete from the `storeAcctComposite` table the shopper's data related to fulfilled orders in the past year

You can use the DELETE statement to remove data from a JSON collection table. The DELETE operation works similarly on fixed schema tables. Consider the JSON collection table created for a shopping application.

    DELETE FROM storeAcctComposite s where s.orders.EstDelivery <any "2023-01-01" AND s.orders.status =any "Delivered" RETURNING contactPhone, id, firstName

Explanation: In the `storeAcctComposite` table, the shoppers don't have a permanent account. The `contactPhone` and `id` are used to track the orders. As a maintenance activity, the DELETE statement above deletes the shoppers' data for which all the orders are already delivered and the delivery dates are before the year 2023.

Output:

    {
      "contactPhone" : "1517113582",
      "id" : 10,
      "firstName" : "Dierdre"
    }