You can use the UPDATE syntax 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
  • The UPDATE statements support the following modifiers:
    • If you specify LOW_PRIORITY, the UPDATE operation is performed after all the read operations on the table are completed.
    • If you specify IGNORE, the errors are ignored during the update process. This indicates that the update is not interrupted by the errors.
  • Each modifier in the UPDATE statements is pushed down to the storage layer MySQL and remains unchanged. This process does not affect the modifier operations of PolarDB-X 1.0.

Limits on syntax

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

  • Correlated and uncorrelated subqueries are not supported in the SET clauses. This limit is illustrated in the following example:
    UPDATE t1 SET name = (SELECT name FROM t2 WHERE t2.id = t1.id) WHERE id > 10;
  • By default, an UPDATE statement is forbidden if the statement needs to update more than 10,000 rows and the statement cannot be pushed down. In this case, you must use hints so that the UPDATE statement can be supported, as shown in the following example:
    UPDATE t1 SET t1.name = "abc" ORDER BY name LIMIT 10001;
UPDATE t1, t2 SET t1.name = t2.name WHERE t1.id = t2.name LIMIT 10001;
    Note The shard key of t1 and t2 is ID.

References

UPDATE syntax for MySQL