====== UPDATE ====== .. container:: refentry :name: SQL-UPDATE .. container:: titlepage .. container:: refnamediv .. rubric:: UPDATE :name: update UPDATE — 更新一个表的行 .. container:: refsynopsisdiv .. rubric:: 大纲 :name: 大纲 .. code:: synopsis [ 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_list ] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] .. container:: refsect1 :name: id-1.9.3.182.5 .. rubric:: 描述 :name: 描述 ``UPDATE``\ 更改满足条件的所有行中指定列 的值。只有要被修改的列需要在\ ``SET``\ 子句中提及， 没有被显式修改的列保持它们之前的值。 有两种方法使用包含在数据库其他表中的信息来修改一个表：使用子选择 或者在\ ``FROM``\ 子句中指定额外的表。这种技术只适合 特定的环境。 可选的\ ``RETURNING``\ 子句导致\ ``UPDATE`` 基于实际被更新的每一行计算并且返回值。任何使用该表的列以及 ``FROM``\ 中提到的其他表的列的表达式都能被计算。 计算时会使用该表的列的新（更新后）值。\ ``RETURNING`` 列表的语法和\ ``SELECT``\ 的输出列表相同。 你必须拥有该表上的\ ``UPDATE``\ 特权，或者至少拥有 要被更新的列上的该特权。如果任何一列的值需要被 ``expressions``\ 或者 ``condition``\ 读取， 你还必须拥有该列上的\ ``SELECT``\ 特权。 .. container:: refsect1 :name: id-1.9.3.182.6 .. rubric:: 参数 :name: 参数 .. container:: variablelist ``with_query`` ``WITH``\ 子句允许你指定一个或者更多个在 ``UPDATE``\ 中可用其名称引用的子查询。详见 `节 `__\ 和\ `SELECT `__\ 。 ``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_list`` 表表达式的列表，允许来自其他表的列出现在\ ``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`` 用于一个被返回列的名称。 .. container:: refsect1 :name: id-1.9.3.182.7 .. rubric:: 输出 :name: 输出 成功完成时，一个\ ``UPDATE``\ 命令返回形如 .. code:: screen UPDATE count 的命令标签。 ``count``\ 是被更新的行数， 包括值没有更改的匹配行。注意，当更新被一个\ ``BEFORE UPDATE`` 触发器抑制时，这个数量可能比匹配 ``condition``\ 的行数少。如果 ``count``\ 为零，没有行被该查 询更新（这不是一个错误）。 如果\ ``UPDATE``\ 命令包含一个\ ``RETURNING`` 子句，其结果将类似于一个包含\ ``RETURNING``\ 列表中定义的 列和值的\ ``SELECT``\ 语句（在被该命令更新的行上计算） 的结果。 .. container:: refsect1 :name: id-1.9.3.182.8 .. rubric:: 注解 :name: 注解 当存在\ ``FROM``\ 子句时，实际发生的是：目标表被连接到 ``from_list``\ 中的表，并且该连接的每一 个输出行表示对目标表的一个更新操作。在使用\ ``FROM`` 时，你应该确保该连接对每一个要修改的行产生至多一个输出行。换 句话说，一个目标行不应该连接到来自其他表的多于一行上。如果发 生这种情况，则只有一个连接行将被用于更新目标行，但是将使用哪 一行是很难预测的。 由于这种不确定性，只在一个子选择中引用其他表更安全，不过这种 语句通常很难写并且也比使用连接慢。 在分区表的情况下，更新一行有可能导致它不再满足其所在分区的分区约束。此时，如果这个行满足分区树中某个其他分区的分区约束，那么这个行会被移动到那个分区。 如果没有这样的分区，则会发生错误。在后台，行的移动实际上是一次\ ``DELETE``\ 操作和一次\ ``INSERT``\ 操作。 在移动的行上的并发\ ``UPDATE``\ 或\ ``DELETE``\ 可能会收到序列化失败错误。 假设会话 1 正在分区键上执行\ ``UPDATE``\ ，同时，对可访问该行的并发会话 2 在此行上执行\ ``UPDATE``\ 或\ ``DELETE``\ 操作。 在这种情况下，会话 2 的\ ``UPDATE`` 或 ``DELETE``\ 将检测行移动并引发序列化失败错误（该错误始终返回 SQLSTATE 代码"40001"）。 如果发生这种情况，应用程序可能希望重试事务。 在通常情况下，表没有分区或没有行移动，会话 2 将标识新更新的行，并执行\ ``UPDATE``/``DELETE``\ 在此新行版本中。 请注意，虽然行可以从本地分区移动到外表分区（如果外数据包装器支持元组路由），但它们不能从外表分区移动到另一个分区。 .. container:: refsect1 :name: id-1.9.3.182.9 .. rubric:: 示例 :name: 示例 把表\ ``films``\ 的列\ ``kind`` 中的单词\ ``Drama``\ 改成\ ``Dramatic``\ ： .. code:: programlisting UPDATE films SET kind = 'Dramatic' WHERE kind = 'Drama'; 在表\ ``weather``\ 的一行中调整温度项并且 把沉淀物重置为它的默认值： .. code:: programlisting UPDATE weather SET temp_lo = temp_lo+1, temp_hi = temp_lo+15, prcp = DEFAULT WHERE city = 'San Francisco' AND date = '2003-07-03'; 执行相同的操作并且返回更新后的项： .. code:: programlisting 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; 使用另一种列列表语法来做同样的更新： .. code:: programlisting 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``\ 子句语法： .. code:: programlisting UPDATE employees SET sales_count = sales_count + 1 FROM accounts WHERE accounts.name = 'Acme Corporation' AND employees.id = accounts.sales_person; 执行相同的操作，在 ``WHERE``\ 子句中使用子选择： .. code:: programlisting UPDATE employees SET sales_count = sales_count + 1 WHERE id = (SELECT sales_person FROM accounts WHERE name = 'Acme Corporation'); 更新 accounts 表中的联系人姓名以匹配当前被分配的销售员： .. code:: programlisting UPDATE accounts SET (contact_first_name, contact_last_name) = (SELECT first_name, last_name FROM salesmen WHERE salesmen.id = accounts.sales_id); 可以用连接完成类似的结果： .. code:: programlisting 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，而第二个查询完全不会更新该行。 更新一个统计表中的统计数据以匹配当前数据： .. code:: programlisting 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); 尝试插入一个新库存项及其库存量。如果该项已经存在，则转而更新 已有项的库存量。要这样做并且不让整个事务失败，可以使用保存点： .. code:: programlisting 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``\ 列： .. code:: programlisting UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; .. container:: refsect1 :name: id-1.9.3.182.10 .. rubric:: 兼容性 :name: 兼容性 这个命令符合SQL标准，不过 ``FROM``\ 和\ ``RETURNING``\ 子句是 PostgreSQL扩展，把 ``WITH``\ 用于\ ``UPDATE``\ 也是扩展。 有些其他数据库系统提供了一个\ ``FROM``\ 选项，在其中在其中目标表 可以在\ ``FROM``\ 中被再次列出。但 PostgreSQL不是这样解释 ``FROM``\ 的。在移植使用这种扩展的应用时要小心。 根据标准，一个目标列名的圆括号子列表的来源值可以是任意得到正确列数的行值 表达式。PostgreSQL只允许来源值是一个 `行构造器 `__\ 或者一个子-``SELECT``\ 。一个列的 被更新值可以在行构造器的情况中被指定为\ ``DEFAULT``\ ，但在 子-``SELECT``\ 的情况中不能这样做。