You can use the UPDATE syntax to modify the rows that meet the conditions in tables.


  • Single logical table.
    UPDATE [LOW_PRIORITY] [IGNORE] [schema_name.]tbl_name
        SET assignment_list
        [WHERE where_condition]
        {expr | DEFAULT}
        col_name = value
        assignment [, assignment] ...
  • Multiple logical tables.
    UPDATE [LOW_PRIORITY] [IGNORE] table_references
        SET assignment_list
        [WHERE where_condition]     
  • 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 = 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 = "abc" ORDER BY name LIMIT 10001;
    UPDATE t1, t2 SET = WHERE = LIMIT 10001;  
    Note The shard key of t1 and t2 is ID.


UPDATE syntax for MySQL