Delete data with SQL

Use Tablestore SDK for Java to execute DELETE statements and delete a single row from a data table for which local transactions are disabled.

Prerequisites

Install the Tablestore SDK for Java and initialize a client.
Use Tablestore SDK for Java 5.17.11 or later.
Create a mapping table for the data table from which you want to delete data, and make sure that local transactions are disabled for the data table.
If you use a RAM user, grant the SQL_DML permission to the RAM user.

Description

Call the sqlQuery method to execute a DELETE statement and delete a single row. In the WHERE clause, use equality conditions to specify all primary key columns.

SQLQueryResponse sqlQuery(SQLQueryRequest request)

The following example deletes the row whose primary key values are 1 and row1 from the example_table data table.

String query = "DELETE FROM example_table "
        + "WHERE tenant_id = 1 AND pk = 'row1'";
SQLQueryRequest request = new SQLQueryRequest(query);

SQLQueryResponse response = client.sqlQuery(request);
System.out.println("Affected rows: " + response.getAffectedRows());

Parameters

SQLQueryRequest contains the following parameter.

Name	Type	Description
query (required)	String	The `DELETE` statement to execute. In the `WHERE` clause, use equality conditions to specify all primary key columns.

Response

SQLQueryResponse contains the following fields related to the delete result.

Field	Type	Description
`affectedRows`	long	Call `getAffectedRows()` to obtain the number of rows that are successfully deleted. The method returns 1 if the row exists and 0 if the row does not exist.
`consumedCapacityByTable`	Map<String, ConsumedCapacity>	Call `getConsumedCapacity()` to obtain the read and write CUs consumed by each data table. The server consumes read CUs to validate the row and write CUs to delete the row.
`type`	SQLStatementType	Call `getSQLStatementType()` to obtain the SQL statement type. `SQL_DELETE` is returned for a `DELETE` statement.