UPDATE

Common Syntax

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

Syntax Constraints

• Subqueries are not supported, including correlated and non-correlated subqueries.
• 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. Do not specify a column for a broadcast table in the left part of a SET statement.
• Updating the sharding key field of a logical table is not supported because this operation may cause data redistribution.
• Setting YYYY of datetime (in the format of YYYY-MM-DD HH:MM:SS) 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.
• Updating sharding keys in subqueries is not allowed for secondary sharded tables that contain JSON fields.
• UPDATE cannot be used to update self-joins.
• Referencing other target 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.