UPDATE

Common Syntax

UPDATE table_reference
SET col_name1={expr1} [, col_name2={expr2}] ...
[WHERE where_condition]

Syntax Restrictions

• Subqueries are not supported, including correlated and non-correlated subqueries.
• Cross-shard subquery is not supported.
• The WHERE condition in the UPDATE statement does not support arithmetic expressions and their subqueries.
• Modifying broadcast tables is not supported during an update of multiple tables. (Data in columns of a broadcast table cannot be on the left of SET assignment statements).
• Updating the sharding key field of a logical table is not supported because this operation may cause data redistribution.
• Setting datetime to 1582 or any value smaller in UPDATE statements is not supported.
• UPDATE cannot be used to update sharding key value DEFAULT.
• Repeatedly updating the same field in an UPDATE statement is not supported.
• Updating a sharding key using UPDATE JOIN syntax is not supported.
• UPDATE cannot be used to update self-joins.
• Referencing other object columns in assignment statements or expressions may cause unexpected update results. Example:

  update tbl_1 a,tbl_2 b set a.name=concat(b.name,'aaaa'),b.name=concat(a.name,'bbbb') on a.id=b.id

• UPDATE JOIN supports only joins with WHERE conditions.