UPDATE语句指南：语法、参数、用法与示例-云原生数据库 PolarDB-阿里云

本文介绍了UPDATE语法的简介、使用方法以及示例等内容。

简介

UPDATE更改满足条件的所有行中指定列的值。只有要被修改的列需要在SET子句中提及，没有被显式修改的列保持它们之前的值。

有两种方法使用包含在数据库其他表中的信息来修改一个表：使用子选择或者在FROM子句中指定额外的表。这种技术只适合特定的环境。

可选的RETURNING子句导致UPDATE 基于实际被更新的每一行计算并且返回值。任何使用该表的列以及 FROM中提到的其他表的列的表达式都能被计算。计算时会使用该表的列的新（更新后）值。RETURNING 列表的语法和SELECT的输出列表相同。

你必须拥有该表上的UPDATE特权，或者至少拥有要被更新的列上的该特权。如果任何一列的值需要被expressions或者condition读取，你还必须拥有该列上的SELECT特权。

语法

    [ WITH [ RECURSIVE ] with_query [, ...] ]
    UPDATE [ ONLY ] table_name [ * ] [ [ AS ] alias ]
        SET { column_name = { expression | DEFAULT } |
              ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
              ( column_name [, ...] ) = ( sub-SELECT )
            } [, ...]
        [ FROM from_item [, ...] ]
        [ WHERE condition | WHERE CURRENT OF cursor_name ]
        [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

参数说明

with_query：WITH子句允许你指定一个或者更多个在 UPDATE中可用其名称引用的子查询。
table_name：要更新的表的名称（可以是模式限定的）。如果在表名前指定了 ONLY，只会更新所提及表中的匹配行。如果没有指定 ONLY，任何从所提及表继承得到的表中的匹配行也会被更新。可选地，在表名之后指定可以显式地指示要把后代表也包括在内。
alias：目标表的替代名称。在提供了别名时，它会完全隐藏表的真实名称。例如，给定UPDATE foo AS f， UPDATE语句的剩余部分必须用 f而不是foo来引用该表。
column_name：table_name 所指定的表的一列的名称。如果需要，该列名可以用一个子域名称或者数组下标限定。不要在目标列的说明中包括表的名称。例如 UPDATE table_name SET table_name.col = 1是非法的。
expression：要被赋值给该列的一个表达式。该表达式可以使用该表中这一列或者其他列的旧值。DEFAULT将该列设置为它的默认值（如果没有为它指定默认值表达式，默认值将会为 NULL）。标识列将设置为关联序列生成的新值。对于生成的列，允许指定此项，但仅指定从其生成表达式计算列的正常行为。
sub-SELECT：SELECT子查询，它产生和在它之前的圆括号列列表中一样多的输出列。被执行时，该子查询必须得到不超过一行。如果它得到一行，其列值会被赋予给目标列。如果它得不到行，NULL 值将被赋予目标列。该子查询可以引用被更新表中当前行的旧值。
from_item：表表达式允许来自其他表的列出现在WHERE条件和更新表达式中。这使用与 SELECT语句的FROM子句相同的语法。例如，可以指定表名的别名。不要将目标表作为 from_list 重复，除非你想做自连接（这种情况下它必须以别名出现在 from_list中）。
condition：返回boolean类型值的表达式。让这个表达式返回true的行将会被更新。
cursor_name：要在WHERE CURRENT OF条件中使用的游标名。要被更新的是从这个游标中最近取出的行。该游标必须是在UPDATE目标表上的非分组查询。注意 WHERE CURRENT OF不能和布尔条件一起指定。有关对游标使用WHERE CURRENT OF的更多信息请见 DECLARE。
output_expression：在每一行被更新后，要被UPDATE命令计算并且返回的表达式。该表达式可以使用 table_name指定的表或者FROM列出的表中的任何列名。写可以返回所有列。
output_name：用于被返回列的名称。

输出

成功完成时，UPDATE命令返回例如以下的命令标签：

    UPDATE count

count是被更新的行数，包括值没有更改的匹配行。

说明

当更新被一个BEFORE UPDATE 触发器抑制时，这个数量可能比匹配 condition的行数少。如果 count为零，没有行被该查询更新（这不是一个错误）。

如果UPDATE命令包含RETURNING 子句，其结果将类似于包含RETURNING列表中定义的列和值的SELECT语句（在被该命令更新的行上计算）的结果。

说明

当存在FROM子句时，实际发生的是：目标表被连接到 from_item列表中的表，并且该连接的每一个输出行表示对目标表的一个更新操作。在使用FROM 时，你应该确保该连接对每个要修改的行产生至多一个输出行。换句话说，一个目标行不应该连接到来自其他表的多于一行上。如果发生这种情况，则只有一个连接行将被用于更新目标行，但是将使用哪一行是很难预测的。

由于这种不确定性，只在一个子选择中引用其他表更安全，不过这种语句通常很难写并且也比使用连接慢。

在分区表的情况下，更新一行有可能导致它不再满足其所在分区的分区约束。此时，如果这个行满足分区树中某个其他分区的分区约束，那么这个行会被移动到那个分区。如果没有这样的分区，则会发生错误。在后台，行的移动实际上是一次DELETE操作和一次INSERT操作。

在移动的行上的并发UPDATE或DELETE可能会收到序列化失败错误。假设会话1正在分区键上执行UPDATE，同时，对可访问该行的并发会话2在此行上执行UPDATE或DELETE操作。在这种情况下，会话2的UPDATE 或 DELETE将检测行移动并引发序列化失败错误（该错误始终返回 SQLSTATE 代码"40001"）。如果发生这种情况，应用程序可能希望重试事务。在通常情况下，表没有分区或没有行移动，会话2将标识新更新的行，并执行UPDATE/DELETE在此新行版本中。

说明

虽然行可以从本地分区移动到外表分区（如果外数据包装器支持元组路由），但它们不能从外表分区移动到另一个分区。

示例

将表films的列kind 中的单词Drama改成Dramatic：

    UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama';

在表weather的一行中调整温度项并且把沉淀物重置为它的默认值：

    UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
      WHERE city = 'San Francisco' AND date = '2003-07-03';

执行相同的操作并且返回更新后的项：

    UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT
      WHERE city = 'San Francisco' AND date = '2003-07-03'
      RETURNING temp_lo, temp_hi, prcp;

使用另一种列列表语法来做同样的更新：

    UPDATE weather SET (temp_lo, temp_hi, prcp) = (temp_lo+1, temp_lo+15, DEFAULT)
      WHERE city = 'San Francisco' AND date = '2003-07-03';

为管理 Acme Corporation 账户的销售人员增加销售量，使用 FROM子句语法：

    UPDATE employees SET sales_count = sales_count + 1 FROM accounts
      WHERE accounts.name = 'Acme Corporation'
      AND employees.id = accounts.sales_person;

执行相同的操作，在 WHERE子句中使用子选择：

    UPDATE employees SET sales_count = sales_count + 1 WHERE id =
      (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation');

更新 accounts 表中的联系人姓名以匹配当前被分配的销售员：

    UPDATE accounts SET (contact_first_name, contact_last_name) =
        (SELECT first_name, last_name FROM salesmen
         WHERE salesmen.id = accounts.sales_id);

可以用连接完成类似的结果：

    UPDATE accounts SET contact_first_name = first_name,
                        contact_last_name = last_name
      FROM salesmen WHERE salesmen.id = accounts.sales_id;

说明

如果salesmen.id不是一个唯一键，第二个查询可能会给出令人意外的结果，然而如果有多个id匹配，第一个查询保证会发生错误。还有，如果对于一个特定的 accounts.sales_id项没有匹配，第一个查询将把相应的姓名域设置为 NULL，而第二个查询完全不会更新该行。

更新一个统计表中的统计数据以匹配当前数据：

    UPDATE summary s SET (sum_x, sum_y, avg_x, avg_y) =
        (SELECT sum(x), sum(y), avg(x), avg(y) FROM data d
         WHERE d.group_id = s.group_id);

尝试插入新库存项及其库存量。如果该项已经存在，则转而更新已有项的库存量。要这样做并且不让整个事务失败，可以使用保存点：

    BEGIN;
    -- 其他操作
    SAVEPOINT sp1;
    INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
    -- 假定上述语句由于未被唯一键失败，
    -- 那么现在我们发出这些命令：
    ROLLBACK TO sp1;
    UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
    -- 继续其他操作，并且最终
    COMMIT;

更改表films中由游标c_films 定位的行的kind列：

    UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films;