Deleting Data
Learn how to delete rows from your 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.
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()
.
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.
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.
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 theAppConfig
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.
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();
}
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();