Update data with SQL

Use Tablestore SDK for Java to execute UPDATE statements and update a single row in 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 in which you want to update 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 an UPDATE statement and update one or more attribute columns in a single row. In the WHERE clause, use equality conditions to specify all primary key columns. Primary key columns cannot be updated.

SQLQueryResponse sqlQuery(SQLQueryRequest request)

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

String query = "UPDATE example_table SET name = 'updated' "
        + "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 `UPDATE` statement to execute. The `SET` clause specifies the attribute columns and values to update. The `WHERE` clause uses equality conditions to specify all primary key columns.

Response

SQLQueryResponse contains the following fields related to the update result.

Field	Type	Description
`affectedRows`	long	Call `getAffectedRows()` to obtain the number of rows that are successfully updated.
`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 update the row.
`type`	SQLStatementType	Call `getSQLStatementType()` to obtain the SQL statement type. `SQL_UPDATE` is returned for an `UPDATE` statement.

Scenario examples

Atomically increment or decrement an integer value

In the SET clause, use attribute_column = attribute_column + integer or attribute_column = attribute_column - integer to atomically increment or decrement an integer attribute column. The following example atomically increments the score column by 5.

String query = "UPDATE example_table SET score = score + 5 "
        + "WHERE tenant_id = 1 AND pk = 'row1'";
SQLQueryRequest request = new SQLQueryRequest(query);

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