Lindorm:UPDATE

Applicable engines and versions

• The UPDATE syntax is applicable only to LindormTable.

• The UPDATE syntax is supported in LindormTable 2.3.2 and later. For information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

Syntax

update_statement ::= UPDATE [hint_clause] table_identifier
                     SET column_identifier = value (',' column_identifier = value ) *
                     WHERE where_clause
where_clause     ::=  relation ( AND|OR relation )*
relation         ::=  column_name operator term
                      | '(' column_name ( ',' column_name )* ')' operator tuple_literal
operator         ::=  '=' | '<' | '>' | '<=' | '>=' | '!=' | IN | IS NOT? NULL | LIKE

Limits

• You cannot use the UPDATE syntax to update primary keys.

• Single-row atomicity is not guaranteed. If an error occurs when you perform increment operations, such as incrementing the value of a field, and a retry is required, the result may be inaccurate due to repeated calculations.

• Cross-row transactions are not supported. If an error occurs when you execute the UPDATE statement to modify multiple rows of data, data in some rows may be updated while data in others rows may fail to be updated.

  Note

  • When you use an expression to update data in non-primary key columns, we recommend that you update a single row of data and specify all primary keys to quickly locate the row that you want to update. For example, a value in a specific column is incremented in this topic.

  • When you perform a scalar update (for example, the update clause is c1=1) and data in some rows fails to be updated, perfrom the update operation again.

Examples

The following sample code provides an example on how to create a table and insert a row of 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 of data.
UPSERT INTO sensor(p1, c1, c2, c3) VALUES(1,1,'a','a');

Sample result:

+----+----+----+----+
| 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    |
+------+------+------+------+

FAQ

• Why does the number of affected rows not meet expectations after the data is updated?

  The data in the search index table and the primary table is synchronized in real time. If you create a search index for the primary table and the update condition hits the index column, the update may fail due to data synchronization latency. As a result, the number of affected rows does not meet expectations. You can re-execute the update operation after the synchronization is complete. For information about the search indexing latency, see the FAQ section of the "Overview" topic.

• What causes a timeout error when you update multiple rows of data at the same time?

  We recommend that you do not update more than ten thousand rows of data at the same time. If you have to do so, we recommend that you specify the _l_operation_timeout_ parameter in the UPDATE statement to control the timeout period, such as UPDATE /*+ _l_operation_timeout_(30000) */ table1 SET a=1 WHERE b=2;. For more information about the _l_operation_timeout_ parameter, see the Parameters of hintOptions section of the "HINT" topic. If the timeout issue persists after you specify the parameter, you can use the Lindorm compute engine to update multiple rows of data at the same time.