UPDATE-PolarDB(PolarDB)-阿里云帮助中心

Use the UPDATE statement to modify rows in a table that meet specified conditions.

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 statement supports the following modifiers:
- If you set LOW_PRIORITY, the UPDATE operation is executed after all read operations on the table are complete.
- If you set IGNORE, the system ignores errors during the update process, and the operation continues.
All modifiers in an UPDATE statement are pushed down to the MySQL storage layer. This does not affect how modifiers operate in PolarDB-X 1.0.

Syntax restrictions

The UPDATE syntax in PolarDB-X 1.0 has the following restrictions compared to the native MySQL UPDATE syntax.

Subqueries are not supported in the SET clause. This includes correlated and non-correlated subqueries. For example:
```
UPDATE t1 SET name = (SELECT name FROM t2 WHERE t2.id = t1.id) WHERE id > 10;
```
By default, UPDATE statements that cannot be pushed down are prohibited if they affect more than 10,000 rows. You can use a HINT to override this restriction. For 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 for t1 and t2 is ID.
By default, full-table UPDATE operations are prohibited. You can use a HINT to override this restriction. For more information, see Automatic protection against high-risk SQL statements.

References

Syntax for the MySQL UPDATE statement.