Using SQL command to delete data

The DELETE statement is used to remove 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.

Example 1: Delete data from a table with a simple WHERE clause.

You delete the data corresponding to a user with a given fullname.

DELETE FROM BaggageInfo 
WHERE fullName = "Bonnie Williams"

Example 2: Delete data from a table with a RETURNING clause.

The RETURNING clause fetches the details of the row to be deleted. In the example below, you are fetching the full name and conf number corresponding to a ticket number which will be deleted.

DELETE FROM BaggageInfo 
WHERE ticketNo = 1762392196147 
RETURNING fullName,confNo

Output:

{"fullName":"Birgit Naquin","confNo":"QD1L0T"}

Note:

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 coordinated using a two-phase commit and lock mechanism. In such cases, it is recommended that the application re-run the DELETE statement.

Example 3: Delete data from stream_acct table based on the last name.

DELETE FROM stream_acct acct1 
WHERE acct1.acct_data.firstName="Adelaide"
 AND acct1.acct_data.lastName="Willard"