DML operations

Single-row insert

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

Multi-row batch insert within the same partition key

Rows that share the same partition key can be atomically batch-inserted, with a maximum of 200 rows per batch. The following example uses a table schema of user_table(pk1 BIGINT, pk2 VARCHAR(1024), value BIGINT), where pk1 is the partition key. Both rows have pk1 = 1, so they belong to the same partition.

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

Usage notes

• Primary key conflict: Inserting a row with an existing primary key returns error code OTSParameterInvalid with a message containing Duplicate entry for key 'PRIMARY'. Existing data is not overwritten.

• Cross-partition batch insert: All rows in a batch INSERT must share the same partition key. Batch inserts across different partition keys fail.

• Batch size limit: A batch INSERT with more than 200 rows is rejected.

• Affected rows: A single-row INSERT returns 1 on success. An N-row batch INSERT returns N on success. A primary key conflict does not return affected rows and directly raises an error.

Update with constant values

Update one or more columns at the same time.

-- Single-column update
UPDATE user_table SET attr_col = 999 WHERE pk_col = 1;

-- Multi-column update
UPDATE user_table SET c1 = 10, c2 = 20, c3 = 'updated' WHERE id = 1;

-- Composite primary key update. The column order in the WHERE clause can differ from the key definition order.
UPDATE user_table SET value = 999 WHERE pk2 = 2 AND pk1 = 1;

Atomic increment and decrement

Atomically increment or decrement integer (BIGINT) attribute columns. This is useful for counters and similar use cases. Only BIGINT attribute columns are supported.

-- 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 removes the column from the row instead of storing a NULL value.

UPDATE user_table SET attr_col = NULL WHERE pk_col = 1;

Usage notes

• WHERE must specify the full primary key with equality conditions: The following cases are not supported and return error code OTSParameterInvalid or OTSUnsupportOperation:

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

  • The WHERE clause references non-primary-key columns.

  • The WHERE clause uses range conditions (>, <, BETWEEN, and similar operators).

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

  • The WHERE clause includes attribute column conditions in addition to the full primary key.

• Primary key columns cannot be modified: UPDATE does not support changing primary key column values through SET.

• Non-existent row: Updating a non-existent row succeeds silently with 0 affected rows and no error.

Usage notes

• WHERE must specify the full primary key with equality conditions: The same constraints as UPDATE apply. Missing WHERE, partial primary key, non-primary-key column conditions, range conditions, IN, LIKE, OR, and additional attribute column conditions are all unsupported.

• Non-existent row: Deleting a non-existent row succeeds silently with 0 affected rows and no error.

SQL syntax limitations

The following SQL syntax is not supported:

Data type limitations

Column options and constraints

CREATE TABLE supports only the following column options: PRIMARY KEY, NOT NULL, NULL, DEFAULT VALUE, and ON UPDATE. AUTO_INCREMENT, UNIQUE, CHECK, COMMENT, and other column options are not supported.

For constraints, only PRIMARY KEY is supported. UNIQUE, FOREIGN KEY, CHECK, and other constraints are not supported.

Special table type limitations

Tables bound to search indexes and alias tables do not support any DML operations (INSERT, UPDATE, DELETE).