Deleting Data

Learn how to delete rows from your table.

After you insert or load data into a table, you can delete the table rows when they are no longer required.

To delete a row from a table:

/* identify the row to delete */
MapValue delKey = new MapValue().put("id", 2);

/* construct the DeleteRequest */
DeleteRequest delRequest = new DeleteRequest().setKey(delKey)
                                              .setTableName("users");
/* Use the NoSQL handle to execute the delete request */
DeleteResult del = handle.delete(delRequest);
/* on success DeleteResult.getSuccess() returns true */
if (del.getSuccess()) {
  // success, row was deleted
} else {
  // failure, row either did not exist or conditional delete failed
}

You can perform a sequence of DeleteRequest operations on a table using the MultiDeleteRequest class.

Single rows are deleted using borneo.DeleteRequest using a primary key value as shown below.

from borneo import DeleteRequest
# DeleteRequest requires table name and primary key
request = DeleteRequest().set_table_name('users') 
request.set_key({'id': 1})
# perform the operation
result = handle.delete(request)
if result.get_success():
# success -- the row was deleted
# if the row didn't exist or was not deleted for any other reason, False is returned

Delete operations can be conditional based on a borneo.Version returned from a get operation.You can perform multiple deletes in a single operation using a value range using borneo.MultiDeleteRequest and borneo.NoSQLHandle.multi_delete() .

Single rows are deleted using nosqldb.DeleteRequest using a primary key value:

key := &types.MapValue{}
key.Put("id", 1)
req := &nosqldb.DeleteRequest{
    TableName: "users",
    Key: key,
}
res, err := client.Delete(req)

Delete operations can be conditional based on a types.Version returned from a get operation.

To delete a row, use delete method. Pass to it the table name and primary key of the row to delete. In addition, you can make delete operation conditional by specifying on a Version of the row that was previously returned by get or put. You can pass it as matchVersion property of the opt argument: { matchVersion: my_version }. Alternatively you may use deleteIfVersion method.

delete and deleteIfVersion methods return Promise of DeleteResult, which is plain JavaScript object, containing success status of the operation.

const NoSQLClient = require('oracle-nosqldb').NoSQLClient;
const client = new NoSQLClient('config.json');

async function deleteRowsFromUsersTable() {
    const tableName = 'users';
    try {
        let result = await client.put(tableName, { id: 1, name: 'John' });

        // Unconditional delete, should succeed
        result = await client.delete(tableName, { id: 1 });
        // Expected output: delete succeeded
        console.log('delete ' + result.success ? 'succeeded' : 'failed');

        // Delete with non-existent primary key, will fail
        result = await client.delete(tableName, { id: 2 });
        // Expected output: delete failed
        console.log('delete ' + result.success ? 'succeeded' : 'failed');

        // Re-insert the row
        result = await client.put(tableName, { id: 1, name: 'John' });
        let version = result.version;

        // Will succeed because the version matches existing row
        result = await client.deleteIfVersion(tableName, { id: 1 }, version);
        // Expected output: deleteIfVersion succeeded
        console.log('deleteIfVersion ' + result.success ?
            'succeeded' : 'failed');

        // Re-insert the row
        result = await client.put(tableName, { id: 1, name: 'John' });

        // Will fail because the last put has changed the row version, so
        // the old version no longer matches.  The result will also contain
        // existing row and its version because we specified returnExisting in
        // the opt argument.
        result = await client.deleteIfVersion(tableName, { id: 1 }, version,
            { returnExisting: true });
        // Expected output: deleteIfVersion failed
        console.log('deleteIfVersion ' + result.success ?
            'succeeded' : 'failed');
        // Expected output: { id: 1, name: 'John' }
        console.log(result.existingRow);
    } catch(error) {
        //handle errors
    }
}

Note that similar to put operations, success results in false value only if trying to delete row with non-existent primary key or because of version mismatch when matching version was specified. Failure for any other reason will result in error. You can delete multiple rows having the same shard key in a single atomic operation using deleteRange method. This method deletes set of rows based on partial primary key (which must be a shard key or its superset) and optional FieldRange which specifies a range of values of one of the other (not included into the partial key) primary key fields.

To delete a row, use DeleteAsync method. Pass to it the table name and primary key of the row to delete. This method takes the primary key value as MapValue. The field names should be the same as the table primary key column names. You may also pass options as DeleteOptions. In addition, you can make delete operation conditional by specifying on a RowVersion of the row that was previously returned by GetAsync or PutAsync. Use DeleteIfVersionAsync method that takes the row version to match. Alternatively, you may use DeleteAsync method and pass the version as MatchVersion property of DeleteOptions.

var client = new NoSQLClient("config.json");
var tableName = "users";
try
{
    var row = new MapValue
    {
        ["id"] = 1,
        ["name"] = "John"
    };

    var putResult = await client.PutAsync(tableName, row);
    Console.WriteLine("Put {0}.",
       putResult.Success ? "succeeded" : "failed");

    var primaryKey = new MapValue
    {
        ["id"] = 1
    };
    // Unconditional delete, should succeed.
    var deleteResult = await client.DeleteAsync(tableName, primaryKey);
    // Expected output: Delete succeeded.
    Console.WriteLine("Delete {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Delete with non-existent primary key, should fail.
    var deleteResult = await client.DeleteAsync(tableName,
        new MapValue
        {
            ["id"] = 200
        });
    // Expected output: Delete failed.
    Console.WriteLine("Delete {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Re-insert the row and get the new row version.
    putResult = await client.PutAsync(tableName, row);
    var version = putResult.Version;
    // Delete should succeed because the version matches existing
    // row.
    deleteResult = await client.DeleteIfVersionAsync(tableName,
        primaryKey, version);
    // Expected output: DeleteIfVersion succeeded.
    Console.WriteLine("DeleteIfVersion {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Re-insert the row
    putResult = await client.PutAsync(tableName, row);
    // This delete should fail because the last put operation has
    // changed the row version, so the old version no longer matches.
    // The result will also contain existing row and its version because
    // we specified ReturnExisting in DeleteOptions.
    deleteResult = await client.DeleteIfVersionAsync(tableName,
        primaryKey, version);
    // Expected output: DeleteIfVersion failed.
    Console.WriteLine("DeleteIfVersion {0}.",
        deleteResult.Success ? "succeeded" : "failed");
    // Expected output: { "id": 1, "name": "John" }
    Console.WriteLine(result.existingRow);
}
catch(Exception ex) {
    // handle exceptions
}

Note that Success property of the result only indicates whether the row to delete was found and for conditional Delete, whether the provided version was matched. If the Delete operation fails for any other reason, an exception will be thrown. You can delete multiple rows having the same shard key in a single atomic operation using DeleteRangeAsync method. This method deletes set of rows based on partial primary key (which must include a shard key) and optional FieldRange which specifies a range of values of one of the other (not included into the partial key) primary key fields.

Use one of these methods to delete the rows from the tables -

NosqlRepository
                        deleteById()

, delete(), deleteAll(Iterable<? extends T> entities), deleteAll() or using

NosqlTemplate
                        delete()

, deleteAll(), deleteById(), deleteInShard(). For details, see SDK for Spring Data API Reference.

Note:

First, create the AppConfig class that extends AbstractNosqlConfiguration class to provide the connection details of the Oracle NoSQL Database. For more details, see Obtaining a NoSQL connection.

In this section, you use the NosqlRepository deleteAll() method to delete the rows from your table.

Create the UsersRepository interface. This interface extends the NosqlRepository interface and provides the entity class and the data type of the primary key in that class as parameterized types to the NosqlRepository interface. The NosqlRepository interface provides methods that are used to retrieve data from the database.

import com.oracle.nosql.spring.data.repository.NosqlRepository;
 
/* The Users is the entity class and Long is the data type of the primary key in the Users class.
   This interface provides methods that return iterable instances of  the Users class. */
 
public interface UsersRepository extends NosqlRepository<Users, Long> {
    Iterable<Users> findAll();
}

In the application, you use the deleteAll() method to delete the existing rows from the table.

@Autowired
private UsersRepository repo;
 
/* Delete all the existing rows if any, from the Users table.*/
repo.deleteAll();