UPDATE语句

UPDATE是DML语句，用于修改表中的行。

一条UPDATE语句可以从一个WITH子句开始，以定义可在内访问的公用表表达式UPDATE。请参见“ WITH（公用表表达式）”。

单表语法：

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] ...

多表语法：

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

对于单表语法，该UPDATE语句使用新值更新命名表中现有行的列。该SET子句指示要修改的列以及应提供的值。每个值都可以作为表达式或关键字DEFAULT来指定，以将列明确设置为其默认值。该WHERE子句（如果给出）指定标识要更新哪些行的条件。没有WHERE子句，将更新所有行。如果ORDER BY指定了子句，则按指定的顺序更新行。该LIMIT子句限制了可以更新的行数。

对于多表语法，UPDATE更新table_references满足条件的每个表中的行。即使每个匹配行多次匹配条件，它也会更新一次。对于多表的语法，ORDER BY而LIMIT不能使用。

对于分区表，此语句的单表形式和多表形式都支持将PARTITION选项用作表引用的一部分。此选项采用一个或多个分区或子分区（或两者）的列表。仅检查列出的分区（或子分区）是否匹配，无论这些分区或子分区中是否存在满足该条件的行，都不会更新where_condition。

注意
与PARTITION与INSERTor REPLACE语句一起使用的情况不同，UPDATE ... PARTITION即使列出的分区（或子分区）中没有行与匹配，否则其他有效的语句也被认为是成功的where_condition。

有关更多信息和示例，请参见“分区选择”。

where_condition是一个表达式，对于要更新的每一行，其值为true。有关表达式语法，请参见“表达式”。

table_references并按“ SELECT语句”中where_condition所述进行指定。

您UPDATE只需要对UPDATE实际更新的中引用的列具有特权。SELECT对于读取但未修改的任何列，您只需要特权。

该UPDATE语句支持以下修饰符：

使用LOW_PRIORITY修饰符，UPDATE延迟执行，直到没有其他客户端从表中读取。这会影响只使用表级锁只存储引擎（例如MyISAM，MEMORY和MERGE）。
使用IGNORE修饰符，即使更新期间发生错误，update语句也不会中止。在唯一键值上发生重复键冲突的行不会更新。更新为会导致数据转换错误的值的行将更新为最接近的有效值。有关更多信息，请参见 IGNORE关键字和严格SQL模式的比较。

UPDATE IGNORE语句（包括具有ORDER BY子句的语句）被标记为对基于语句的复制不安全。（这是因为顺序的行被更新的行被忽略决定。）这样的陈述在错误日志中产生一个警告使用基于语句的模式下使用时，使用基于行的格式被写入二进制日志MIXED模式。（缺陷号11758262，缺陷号50439）有关更多信息，请参见“复制格式”。

如果您访问表中要在表达式中更新UPDATE的列，请使用该列的当前值。例如，以下语句设置col1为比当前值大一：

UPDATE t1 SET col1 = col1 + 1;

以下语句中的第二个赋值设置col2为当前（更新）col1值，而不是原始col1值。结果是col1和col2具有相同的值。此行为不同于标准SQL。

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

单表UPDATE分配通常从左到右进行评估。对于多表更新，不能保证以任何特定顺序执行分配。

如果将列设置为其当前值，MySQL会注意到这一点，并且不会对其进行更新。

如果更新NOT NULL通过设置为声明的列，并且NULL启用了严格SQL模式，则会发生错误；否则，请执行以下步骤。否则，该列将设置为该列数据类型的隐式默认值，并且警告计数将增加。隐式默认值0用于数字类型，空字符串（''）用于字符串类型，“零”值用于日期和时间类型。请参见“数据类型默认值”。

如果显式更新了生成的列，则唯一允许的值为DEFAULT。有关生成的列的信息，请参见“创建表和生成的列”。

UPDATE返回实际更改的行数。在mysql_info()C API函数返回被匹配和更新的行数，并在发生警告的数量UPDATE。

您可以用来限制的范围。甲子句是行匹配限制。一旦找到满足该子句的行（无论实际上是否已更改），该语句就会停止。LIMIT row_countUPDATELIMITrow_countWHERE

如果一条UPDATE语句包含一个ORDER BY子句，则按该子句指定的顺序更新行。在某些可能导致错误的情况下，这很有用。假设一个表t包含一个id具有唯一索引的列。以下语句可能会因重复键错误而失败，具体取决于行的更新顺序：

UPDATE t SET id = id + 1;

例如，如果表在id列中包含1和2，并且在2更新为3之前将1更新为2，则会发生错误。为避免此问题，请添加一个ORDER BY子句以使具有较大id值的行在具有较小值的行之前进行更新：

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

您还可以执行UPDATE涵盖多个表的操作。但是，您不能使用ORDER BY或LIMIT与multi-table一起使用UPDATE。该table_references子句列出了参与连接的表。“ JOIN子句”中描述了其语法。这是一个例子：

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

前面的示例显示了使用逗号运算符的内部联接，但是多表UPDATE语句可以使用语句中允许的任何类型的联接SELECT，例如LEFT JOIN。

如果您使用UPDATE包含InnoDB有外键约束的表的多表语句，则MySQL优化器可能以与其父/子关系不同的顺序处理表。在这种情况下，该语句将失败并回滚。而是，更新一个表并依靠提供的ON UPDATE功能InnoDB来相应地修改其他表。请参见“外键约束”。

您不能更新表并直接在子查询中从同一表中选择。您可以通过使用多表更新来解决此问题，在该更新中，其中一个表是从您实际希望更新的表派生的，并使用别名引用派生表。假设您希望更新一个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
);

要降低加价幅度为30％或以上且您的存货少于100的任何商品的零售价格，您可以尝试使用以下UPDATE语句（如以下语句），该语句在WHERE子句中使用子查询。如此处所示，此语句不起作用：

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子句中引用它，如下所示：

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_merge将optimizer_switch系统变量的标志设置为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;

在这种情况下使用优化程序提示的优点是，它仅适用于使用该查询器的查询块，因此optimizer_switch在执行之后不必再次更改值UPDATE。

另一种可能性是重写子查询，使其不使用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;

在这种情况下，默认情况下将实现子查询而不是合并子查询，因此不必禁用派生表的合并。