You can execute UPDATE statements to modify the rows that meet the conditions in tables.

Syntax

  • Single logical table
    UPDATE [LOW_PRIORITY] [IGNORE] [schema_name.]tbl_name
    SET assignment_list
    [WHERE where_condition]

value:
    {expr | DEFAULT}

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...
  • Multiple logical tables
    UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]
Note
  • UPDATE statements support the following modifiers:
    • LOW_PRIORITY: If you specify LOW_PRIORITY, the UPDATE operation is performed after all read operations on the table are performed.
    • IGNORE: If you specify IGNORE, the errors are ignored when rows are updated. This indicates that the update is not interrupted by the errors.
  • Each modifier in UPDATE statements is pushed down to ApsaraDB RDS for MySQL and remains unchanged. This process does not affect the modifier operations of PolarDB-X.

Limits on syntax

Compared with the UPDATE syntax of the native MySQL, the UPDATE syntax of PolarDB-X has the following limits:

Correlated and uncorrelated subqueries are not supported in SET clauses. This following code block provides an example on correlated subqueries in SET clauses.
UPDATE t1 SET name = (SELECT name FROM t2 WHERE t2.id = t1.id) WHERE id > 10;
Note The shard key of t1 and t2 is ID.

References

UPDATE statements for the native MySQL