Tablestore:DML operations

Single-row insert

INSERT INTO user_table VALUES (1, 'value1', 100);

Batch insert in a single partition

Multiple rows that share the same partition key can be atomically inserted in a single batch of up to 200 rows. For example, assume the table structure is user_table(pk1 BIGINT, pk2 VARCHAR(1024), value BIGINT), where pk1 is the partition key. Two rows that both have a partition key pk1 of 1 belong to the same partition and can therefore be atomically inserted in a batch.

INSERT INTO user_table VALUES (1, 'attr1', 100), (1, 'attr2', 200);

Notes

• Primary key conflict: Inserting a row with an existing primary key returns an error with the error code OTSParameterInvalid and an error message that contains Duplicate entry for key 'PRIMARY'. The existing data is not overwritten.

• Cross-partition batch inserts: All rows in a batch INSERT statement must have the same partition key.

• Batch size limit: A batch INSERT statement that contains more than 200 rows is rejected.

• Affected rows: A successful single-row INSERT returns 1. A successful N-row batch insert returns N. If a primary key conflict occurs, the operation returns an error instead of the number of affected rows.

Update with constant values

You can update single or multiple columns simultaneously.

-- Update a single column
UPDATE user_table SET attr_col = 999 WHERE pk_col = 1;

-- Update multiple columns
UPDATE user_table SET c1 = 10, c2 = 20, c3 = 'updated' WHERE id = 1;

-- Update a row with a composite primary key. The order of columns in the WHERE clause can differ from the key definition.
UPDATE user_table SET value = 999 WHERE pk2 = 2 AND pk1 = 1;

Atomic increment and decrement

You can atomically increment or decrement the value of a BIGINT attribute column. This is useful for scenarios such as counters. This operation is supported only for integer attribute columns, not for other types such as DOUBLE, BOOL, or VARCHAR.

-- Increment by 1
UPDATE user_table SET counter = counter + 1 WHERE id = 1;

-- Increment by a specified value
UPDATE user_table SET counter = counter + 10 WHERE id = 1;

-- Decrement
UPDATE user_table SET counter = counter - 5 WHERE id = 1;

Set an attribute column to NULL

Setting an attribute column to NULL effectively deletes it; the system removes the column from the row rather than storing a NULL value.

UPDATE user_table SET attr_col = NULL WHERE pk_col = 1;

Notes

• The WHERE clause must specify an equality condition for the complete primary key: The following cases are not supported, and an OTSParameterInvalid or OTSUnsupportOperation error is returned:

  • The WHERE clause does not include the full primary key.

  • The WHERE clause uses a condition on a non-primary key column.

  • The WHERE clause uses a range query (such as >, <, or BETWEEN).

  • The WHERE clause uses non-equality conditions such as IN, LIKE, OR, or !=.

  • The WHERE clause includes a condition on an attribute column in addition to the full primary key.

• Cannot modify primary key columns: The SET clause cannot be used to modify the value of a primary key column.

• Target row does not exist: An UPDATE operation on a non-existent row completes silently with 0 affected rows. No error is returned.

Notes

• The WHERE clause must be an equality condition on the complete primary key: This constraint is the same for UPDATE statements. Missing WHERE clauses, conditions on partial or non-primary key columns, range conditions, the IN, LIKE, or OR operators, or conditions on additional attribute columns are not supported.

• Target row does not exist: A DELETE operation on a non-existent row completes silently with 0 affected rows. No error is returned.

SQL syntax limitations

DML operations on non-transactional tables do not support the following SQL syntax:

Data type limitations

Column options and constraints

When using CREATE TABLE, only the following column options are supported: PRIMARY KEY, NOT NULL, NULL, DEFAULT VALUE, and ON UPDATE.

Only the PRIMARY KEY constraint is supported.

Special table type limitations

Search-indexed tables and alias tables do not support any DML operations (INSERT, UPDATE, DELETE).