UPDATE

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