Lindorm:UPDATE

Applicable engines and versions

• Applies to LindormTable only.

• Requires LindormTable 2.3.2 or later. To check or upgrade your version, see Version guide for LindormTable and Perform a minor version update.

Syntax

UPDATE [hint_clause] table_identifier
SET column_identifier = value (',' column_identifier = value)*
WHERE where_clause

Parameters

`where_clause` structure

where_clause ::=  relation ( AND|OR relation )*
relation     ::=  column_name operator term
                   | '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator     ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | IS NOT? NULL | LIKE

Limitations

• Primary keys cannot be updated. A primary key uniquely identifies a row. To insert a new row, use the UPSERT statement instead.

• Multi-row atomicity is not guaranteed. In a batch update (for example, UPDATE sensor SET c1 = 2, c2=4, c4=6 WHERE p1 = 1;), some rows may succeed while others fail, even when no explicit error is reported.

• Cross-row transactions are not supported. This has the same behavior as the multi-row atomicity limitation above.

• Idempotence is not guaranteed. Expression-based updates (such as c1 = c1 + 1) are not safe to retry — each retry increments the value again. Scalar updates (such as c1 = 2) are idempotent and safe to retry on failure.

  Type	Example	Safe to retry?
  Scalar update	UPDATE sensor SET c1 = 2 WHERE p1 = 1;	Yes
  Expression update	UPDATE sensor SET c1 = c1 + 1 WHERE p1 = 1;	No

Examples

The examples in this section use the following table and initial data:

-- Create a table named sensor
CREATE TABLE sensor (
  p1 INTEGER NOT NULL,
  c1 INTEGER,
  c2 VARCHAR,
  c3 VARCHAR,
  PRIMARY KEY(p1)
);

-- Insert a row
UPSERT INTO sensor(p1, c1, c2, c3) VALUES(1, 1, 'a', 'a');

Initial state:

+----+----+----+----+
| p1 | c1 | c2 | c3 |
+----+----+----+----+
| 1  | 1  | a  | a  |
+----+----+----+----+

UPDATE sensor SET c2='b' WHERE p1=1;

+----+----+----+----+
| p1 | c1 | c2 | c3 |
+----+----+----+----+
| 1  | 1  | b  | a  |
+----+----+----+----+

UPDATE sensor SET c3='b' WHERE c1=1;

+----+----+----+----+
| p1 | c1 | c2 | c3 |
+----+----+----+----+
| 1  | 1  | b  | b  |
+----+----+----+----+

UPDATE sensor SET c1 = c1 + 1 WHERE p1 = 1;

+------+------+------+------+
| p1   | c1   | c2   | c3   |
+------+------+------+------+
|    1 |    2 | b    | b    |
+------+------+------+------+

Batch updates

Keep single batch UPDATE operations under 10,000 rows. For larger datasets, split the operation into smaller batches.

If a batch update times out, add the _l_operation_timeout_ hint to increase the timeout limit (in milliseconds):

UPDATE /*+ _l_operation_timeout_(30000) */ table1 SET a=1 WHERE b=2;

For very large batch updates that exceed what the timeout hint can accommodate, use the Lindorm compute engine instead. For more information about the _l_operation_timeout_ hint, see HINT parameter descriptions.

FAQ

After an update completes, why is `AFFECTED ROWS` lower than expected?

The search index and the primary table are not strongly consistent — there can be a lag before the index reflects recent writes. If your WHERE clause filters on an indexed column, the update may miss rows that have not yet been synced to the index. Wait for synchronization to complete and re-run the update. For details on search index synchronization latency, see the FAQ.