UPDATE is a DML statement that modifies rows in a table.是一个DML语句，用于修改表中的行。

An UPDATE statement can start with a WITH clause to define common table expressions accessible within the UPDATE. UPDATE语句可以以with子句开头，以定义在UPDATE中可访问的公共表表达式。See Section 13.2.15, “WITH (Common Table Expressions)”.见第13.2.15节，“WITH（公共表表达式）”。

Single-table syntax:单表语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET assignment_list
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]
value:
    {expr | DEFAULT}
assignment:
col_name = value
assignment_list:
assignment [, assignment] ...

Multiple-table syntax:多表语法：

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET assignment_list
    [WHERE where_condition]

For the single-table syntax, the UPDATE statement updates columns of existing rows in the named table with new values. 对于单表语法，UPDATE语句用新值更新命名表中现有行的列。The SET clause indicates which columns to modify and the values they should be given. SET子句指示要修改哪些列以及应该给出哪些列的值。Each value can be given as an expression, or the keyword DEFAULT to set a column explicitly to its default value. 可以将每个值作为表达式或关键字DEFAULT来指定，以便将列显式设置为其默认值。The WHERE clause, if given, specifies the conditions that identify which rows to update. WHERE子句（如果给定）指定标识要更新的行的条件。With no WHERE clause, all rows are updated. 如果没有WHERE子句，则所有行都将更新。If the ORDER BY clause is specified, the rows are updated in the order that is specified. 如果指定了ORDER BY子句，则按指定的顺序更新行。The LIMIT clause places a limit on the number of rows that can be updated.LIMIT子句限制可以更新的行数。

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. 对于多表语法，UPDATE更新在table_references中指定的每个表中满足条件的行。Each matching row is updated once, even if it matches the conditions multiple times. 每个匹配行更新一次，即使它多次匹配条件。For multiple-table syntax, ORDER BY and LIMIT cannot be used.对于多表语法，不能使用ORDER BY和LIMIT。

For partitioned tables, both the single-single and multiple-table forms of this statement support the use of a PARTITION clause as part of a table reference. 对于分区表，此语句的单个和多个表形式都支持将PARTITION子句用作表引用的一部分。This option takes a list of one or more partitions or subpartitions (or both). 此选项获取一个或多个分区或子分区（或两者）的列表。Only the partitions (or subpartitions) listed are checked for matches, and a row that is not in any of these partitions or subpartitions is not updated, whether it satisfies the where_condition or not.只检查列出的分区（或子分区）是否匹配，不在这些分区或子分区中的行不更新，无论它是否满足where_condition。

For more information and examples, see Section 24.5, “Partition Selection”.有关更多信息和示例，请参阅第24.5节，“分区选择”。

where_condition is an expression that evaluates to true for each row to be updated. For expression syntax, see Section 9.5, “Expressions”.where_condition是一个表达式，对于要更新的每一行，它的计算结果都为true。有关表达式语法，请参阅第9.5节，“表达式”。

table_references and where_condition are specified as described in Section 13.2.10, “SELECT Statement”.table_references和where_condition被指定为如第13.2.10节，“SELECT语句”所述。

You need the UPDATE privilege only for columns referenced in an UPDATE that are actually updated. 只有在实际更新的更新中引用的列才需要UPDATE权限。You need only the SELECT privilege for any columns that are read but not modified.对于已读取但未修改的任何列，您只需要SELECT权限。

The UPDATE statement supports the following modifiers:UPDATE语句支持以下修饰符：

UPDATE IGNORE statements, including those having an ORDER BY clause, are flagged as unsafe for statement-based replication. UPDATE IGNORE语句（包括具有ORDER BY子句的语句）对于基于语句的复制被标记为不安全的。(This is because the order in which the rows are updated determines which rows are ignored.) （这是因为行的更新顺序决定了忽略哪些行。）Such statements produce a warning in the error log when using statement-based mode and are written to the binary log using the row-based format when using MIXED mode. 当使用基于语句的模式时，此类语句在错误日志中生成警告，当使用MIXED模式时，将使用基于行的格式写入二进制日志。(Bug #11758262, Bug #50439) See Section 17.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”, for more information.有关更多信息，请参阅第17.2.1.3节，“确定二进制日志记录中的安全和不安全语句”。

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. 如果访问要在表达式中更新的表中的列，UPDATE将使用该列的当前值。For example, the following statement sets col1 to one more than its current value:例如，以下语句将col1设置为比其当前值多一个：

UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. 下面语句中的第二个赋值将col2设置为当前（更新的）col1值，而不是原始的col1值。The result is that col1 and col2 have the same value. 结果是col1和col2具有相同的值。This behavior differs from standard SQL.此行为与标准SQL不同。

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. 单表UPDATE分配通常从左到右进行计算。For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.对于多个表更新，不能保证按任何特定顺序执行分配。

If you set a column to the value it currently has, MySQL notices this and does not update it.如果将列设置为当前的值，MySQL会注意到这一点，并且不会更新它。

If you update a column that has been declared NOT NULL by setting to NULL, an error occurs if strict SQL mode is enabled; otherwise, the column is set to the implicit default value for the column data type and the warning count is incremented. 如果通过设置为NULL来更新已声明为NOT NULL的列，则在启用严格SQL模式时会发生错误；否则，列将被设置为列数据类型的隐式默认值，并且警告计数将递增。The implicit default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. 对于数值类型，隐式默认值为0；对于字符串类型，隐式默认值为空字符串（''）；对于日期和时间类型，隐式默认值为“0”。See Section 11.6, “Data Type Default Values”.请参阅第11.6节，“数据类型默认值”。

If a generated column is updated explicitly, the only permitted value is DEFAULT. 如果显式更新生成的列，则唯一允许的值是DEFAULT。For information about generated columns, see Section 13.1.20.8, “CREATE TABLE and Generated Columns”.有关生成列的信息，请参阅第13.1.20.8节，“创建表和生成列”。

UPDATE returns the number of rows that were actually changed. UPDATE返回实际更改的行数。The mysql_info() C API function returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE.mysql_info()C API函数返回匹配和更新的行数以及UPDATE过程中出现的警告数。

You can use LIMIT row_count to restrict the scope of the UPDATE. 可以使用LIMIT row_count来限制UPDATE的作用域。A LIMIT clause is a rows-matched restriction. LIMIT子句是行匹配的限制。The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.语句在找到满足WHERE子句的row_count行后立即停止，无论它们是否实际被更改。

If an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified by the clause. 如果UPDATE语句包含ORDER BY子句，则按该子句指定的顺序更新行。This can be useful in certain situations that might otherwise result in an error. 这在某些可能导致错误的情况下非常有用。Suppose that a table t contains a column id that has a unique index. 假设表t包含具有唯一索引的列id。The following statement could fail with a duplicate-key error, depending on the order in which rows are updated:根据行的更新顺序，以下语句可能会因重复键错误而失败：

UPDATE t SET id = id + 1;

For example, if the table contains 1 and 2 in the id column and 1 is updated to 2 before 2 is updated to 3, an error occurs. 例如，如果表的id列中包含1和2，并且在将2更新为3之前将1更新为2，则会发生错误。To avoid this problem, add an ORDER BY clause to cause the rows with larger id values to be updated before those with smaller values:要避免此问题，请添加ORDER BY子句，以使id值较大的行在id值较小的行之前更新：

UPDATE t SET id = id + 1 ORDER BY id DESC;

You can also perform UPDATE operations covering multiple tables. 您还可以执行覆盖多个表的UPDATE操作。However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. 但是，不能对多表更新使用ORDER BY或LIMIT。The table_references clause lists the tables involved in the join. table_references子句列出了联接中涉及的表。Its syntax is described in Section 13.2.10.2, “JOIN Clause”. 其语法在第13.2.10.2节，“JOIN子句”中描述。Here is an example:举个例子：

UPDATE items,month SET items.price=month.price
WHERE items.id=month.id;

The preceding example shows an inner join that uses the comma operator, but multiple-table UPDATE statements can use any type of join permitted in SELECT statements, such as LEFT JOIN.前面的示例显示了使用逗号运算符的内部联接，但是多个表UPDATE语句可以使用SELECT语句中允许的任何类型的联接，例如LEFT JOIN。

If you use a multiple-table UPDATE statement involving InnoDB tables for which there are foreign key constraints, the MySQL optimizer might process tables in an order that differs from that of their parent/child relationship. 如果您使用一个涉及InnoDB表的多表UPDATE语句，其中有外键约束，那么MySQL优化器可能会按照不同于表的父/子关系的顺序来处理表。In this case, the statement fails and rolls back. 在这种情况下，语句失败并回滚。Instead, update a single table and rely on the ON UPDATE capabilities that InnoDB provides to cause the other tables to be modified accordingly. 相反，更新一个表并依赖InnoDB提供的ON UPDATE功能来相应地修改其他表。See Section 13.1.20.5, “FOREIGN KEY Constraints”.请参阅第13.1.20.5节，“外键约束”。

You cannot update a table and select directly from the same table in a subquery. 不能更新表并直接从子查询中的同一表中进行选择。You can work around this by using a multi-table update in which one of the tables is derived from the table that you actually wish to update, and referring to the derived table using an alias. 您可以通过使用多表更新来解决此问题，其中一个表是从实际要更新的表派生的，并使用别名引用派生表。Suppose you wish to update a table named items which is defined using the statement shown here:假设您希望更新名为items的表，该表是使用以下语句定义的：

CREATE TABLE items (
    id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    wholesale DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    retail DECIMAL(6,2) NOT NULL DEFAULT 0.00,
    quantity BIGINT NOT NULL DEFAULT 0
);

To reduce the retail price of any items for which the markup is 30% or greater and of which you have fewer than one hundred in stock, you might try to use an UPDATE statement such as the one following, which uses a subquery in the WHERE clause. 要降低任何商品的零售价，如果这些商品的加价是30%或更高，并且您的库存少于100个，您可以尝试使用UPDATE语句，例如下面的语句，它在WHERE子句中使用子查询。As shown here, this statement does not work:如图所示，此语句不起作用：

mysql> UPDATE items
     > SET retail = retail * 0.9
     > WHERE id IN
     >     (SELECT id FROM items
     >         WHERE retail / wholesale >= 1.3 AND quantity > 100);
ERROR 1093 (HY000): You can't specify target table 'items' for update in FROM clause

Instead, you can employ a multi-table update in which the subquery is moved into the list of tables to be updated, using an alias to reference it in the outermost WHERE clause, like this:相反，您可以使用多表更新，其中子查询被移动到要更新的表列表中，使用别名在最外层的WHERE子句中引用它，如下所示：

UPDATE items,
       (SELECT id FROM items
        WHERE id IN
            (SELECT id FROM items
             WHERE retail / wholesale >= 1.3 AND quantity < 100))
        AS discounted
SET items.retail = items.retail * 0.9
WHERE items.id = discounted.id;

Because the optimizer tries by default to merge the derived table discounted into the outermost query block, this works only if you force materialization of the derived table. 因为优化器在默认情况下会尝试将派生表discounted到最外层的查询块中，所以只有在强制对派生表进行物化时，这种方法才有效。You can do this by setting the derived_merge flag of the optimizer_switch system variable to off before running the update, or by using the NO_MERGE optimizer hint, as shown here:您可以在运行更新之前将optimizer_switch系统变量的derived_merge标志设置为off，或者使用NO_MERGE优化器提示来完成此操作，如下所示：

UPDATE /*+ NO_MERGE(discounted) */ items,
       (SELECT id FROM items
        WHERE retail / wholesale >= 1.3 AND quantity < 100)
        AS discounted
    SET items.retail = items.retail * 0.9
    WHERE items.id = discounted.id;

The advantage of using the optimizer hint in such a case is that it applies only within the query block where it is used, so that it is not necessary to change the value of optimizer_switch again after executing the UPDATE.在这种情况下使用优化器提示的优点是，它只应用于使用它的查询块中，因此在执行更新后不必再次更改optimizer_switch的值。

Another possibility is to rewrite the subquery so that it does not use IN or EXISTS, like this:另一种可能是重写子查询，w从而它不使用IN或EXISTS，如下所示：

UPDATE items,
       (SELECT id, retail / wholesale AS markup, quantity FROM items)
       AS discounted
    SET items.retail = items.retail * 0.9
    WHERE discounted.markup >= 1.3
    AND discounted.quantity < 100
    AND items.id = discounted.id;

In this case, the subquery is materialized by default rather than merged, so it is not necessary to disable merging of the derived table.在这种情况下，子查询在默认情况下是具体化的，而不是合并的，因此不必禁用派生表的合并。