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

UPDATE 语句可以以 WITH 子句来定义在 UPDATE。参见 第 15.2.20 节，“WITH （Common Table Expressions）”。An UPDATE statement can start with a WITH clause to define common table expressions accessible within the UPDATE. See Section 15.2.20, “WITH (Common Table Expressions)”.

单表语法：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]

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

对于多表语法，UPDATE 更新满足条件的每个表中 table_references 中行。每个匹配的行更新一次，即使多次符合条件。对于多表语法，ORDER BY 和 LIMIT。For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions. 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.

对于分区表，该语句的单表和多表形式都支持在表引用中使用一个PARTITION子句。此选项接受一个或多个分区或子分区（或两者）的列表。只有列出的分区（或子分区）会被检查匹配，如果行不在任何这些分区或子分区中，无论是否满足where_condition，该行都不会被更新。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. 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.

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

where_condition 是一个对每行更新都值为真的表达式。关于表达式语法，请参见 第 11.5 节，“表达式”。where_condition is an expression that evaluates to true for each row to be updated. For expression syntax, see Section 11.5, “Expressions”.

table_references以及where_condition are specified as described in 第 15.2.13 节，“SELECT 语句”.table_references and where_condition are specified as described in Section 15.2.13, “SELECT Statement”.

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

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

UPDATE IGNORE 语句，包括那些带有 ORDER BY 子句的语句，在基于语句的复制中被标记为不安全。（这是因为更新行的顺序决定了哪些行会被忽略。）在使用基于语句模式时，这类语句会在错误日志中产生警告，并且在使用 MIXED 模式时，会以基于行的格式写入二进制日志。（Bug #11758262, Bug #50439）有关更多信息，请参见 第 19.2.1.3 节，“二进制日志中安全和不安全语句的判断”。UPDATE IGNORE statements, including those having an ORDER BY clause, are flagged as unsafe for statement-based replication. (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. (Bug #11758262, Bug #50439) See Section 19.2.1.3, “Determination of Safe and Unsafe Statements in Binary Logging”, for more information.

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

UPDATE t1 SET col1 = col1 + 1;

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

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

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

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

如果你更新了一个已经通过NOT NULL声明的列并将其设置为NULL，则在启用严格 SQL 模式时会出现错误；否则，该列会被设置为列数据类型的隐式默认值，并增加警告计数。隐式默认值是0，对于字符串类型是空字符串（''），对于日期和时间类型是“零”日期和时间类型的值。请参阅第 13.6 节，“数据类型默认值”.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. The implicit default value is 0 for numeric types, the empty string ('') for string types, and the “zero” value for date and time types. See Section 13.6, “Data Type Default Values”.

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

UPDATE 返回实际更改的行数。mysql_info() C API 函数返回在 UPDATE 期间被匹配和更新的行数以及产生的警告数。UPDATE returns the number of rows that were actually changed. 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.

您可以使用LIMIT row_count以限制UPDATE的范围LIMIT条款是按行匹配的限制。该语句一旦找到row_count满足WHERE条款的行，无论它们是否实际被更改都会停止。You can use LIMIT row_count to restrict the scope of the UPDATE. A LIMIT clause is a rows-matched restriction. The statement stops as soon as it has found row_count rows that satisfy the WHERE clause, whether or not they actually were changed.

如果 UPDATE 语句包含 ORDER BY 子句，则行按子句指定的顺序更新。这在某些情况下可能引发错误时非常有用。假设一个表 t 包含一个具有唯一索引的列 id。以下语句可能会因重复键错误而失败，具体取决于更新行的顺序：If an UPDATE statement includes an ORDER BY clause, the rows are updated in the order specified by the clause. 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. 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;

例如，如果表中 id，且 1 在 2 更新为 3 前先被更新为 2，就会发生错误。为避免此问题，添加 ORDER BY 子句，使得 id 值较小的行先更新：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. 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:

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

You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY或者LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in 第 15.2.13.2 节，“JOIN 子句”. Here is an example:You can also perform UPDATE operations covering multiple tables. However, you cannot use ORDER BY or LIMIT with a multiple-table UPDATE. The table_references clause lists the tables involved in the join. Its syntax is described in Section 15.2.13.2, “JOIN Clause”. Here is an example:

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

前面的例子展示了使用逗号操作符的内部连接，但多重表 UPDATE 语句可以使用允许的 join 类型 SELECT 语句，例如 LEFT JOINThe 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 语句，涉及包含外键约束的 InnoDB 表，MySQL 优化器可能会以与父/子关系不同的顺序处理表。此时，该语句失败并回滚。相反，更新单个表，依靠 ON UPDATE 功能，InnoDB 提供的，从而相应地修改其他表。参见 第 15.1.20.5 节，“FOREIGN KEY Constraints”。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. 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. See Section 15.1.20.5, “FOREIGN KEY Constraints”.

你不能在子查询中直接从同一张表中更新表。你可以通过多表更新来绕过这个问题，其中一个表是从你真正想更新的表派生的，并通过别名引用该派生表。假设你想更新一个名为 items（通过此处显示的语句定义的表）：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:

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
);

为了降低任何加价 30%或以上且库存少于 100 件的商品零售价格，你可以尝试使用UPDATE语句，比如以下，它使用WHERE。如图所示，这个语句不起作用：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. 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

相反，你可以采用多表更新，将子查询移入待更新表列表，使用别名在最外层的 WHERE 子句中引用，比如这样：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:

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;

由于优化器默认尝试将派生表discounted合并到最外层查询块，这只有在强制实现派生表的物质化时才有效。你可以在更新前将 derived_mergeoptimizer_switch 系统变量 于 off，或者使用 NO_MERGE优化器提示，如图所示：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. 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:

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;

在这种情况下使用优化器提示的优点是它仅适用于查询块内，因此在执行optimizer_switch。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.

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

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.