更新

UPDATE — 更新表中的行

语法

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

说明

UPDATE会更改满足条件的所有行中指定列的值。只有要修改的列才需要在SET子句中提及；未明确修改的列会保留其以前的值。

有两种方法可以使用数据库中其他表中的信息修改表：使用子查询，或在FROM子句中指定其他表。哪种技术更合适取决于具体情况。

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

你必须对表具有UPDATE权限，或至少对要更新的列具有UPDATE权限。你还必须对在*expressions或condition*中读取其值的任何列具有SELECT权限。

参数

with_query

WITH 子句允许你指定一个或多个子查询，这些子查询可以通过名称在 UPDATE 查询中引用。有关详细信息，请参见 第 7.8 节 和 SELECT。

table_name

要更新的表（可选的模式限定）的名称。如果在表名前指定 ONLY，则仅在指定的表中更新匹配的行。如果未指定 ONLY，则还将在继承自指定表的任何表中更新匹配的行。此外，可以在表名后指定 * 以明确表示包括后代表。

alias

目标表的替代名称。提供别名时，它会完全隐藏表的实际名称。例如，给定 UPDATE foo AS f，UPDATE 语句的其余部分必须将此表称为 f，而不是 foo。

列名

表名 指定的表中的列名。如有需要，列名可以使用子字段名或数组下标进行限定。不要在目标列的规范中包含表名，例如，UPDATE table_name SET table_name.col = 1 是无效的。

表达式

分配给列的表达式。该表达式可以使用表中此列和其他列的旧值。

DEFAULT

将列设置为其默认值（如果未为其分配特定的默认表达式，则为 NULL）。标识列将设置为关联序列生成的新值。对于生成列，允许指定此项，但仅仅指定从其生成表达式计算列的正常行为。

子选择

一个 SELECT 子查询，生成与前面括号中列列表中列数一样多的输出列。执行时，子查询必须不生成超过一行。如果生成一行，则其列值分配给目标列；如果未生成行，则 NULL 值分配给目标列。子查询可以引用正在更新的表的当前行的旧值。

from_item

允许其他表中的列出现在 WHERE 条件和更新表达式中的表表达式。这使用与 SELECT 语句的 FROM 子句相同的语法；例如，可以指定表名的别名。不要重复目标表作为 from_item，除非您打算进行自连接（在这种情况下，它必须在 from_item 中以别名出现）。

条件

返回 boolean 类型的值的表达式。仅当此表达式返回 true 的行才会被更新。

游标名

在 WHERE CURRENT OF 条件中使用的游标的名称。要更新的行是最近从此游标中获取的行。游标必须是 UPDATE 目标表上的非分组查询。请注意，WHERE CURRENT OF 不能与布尔条件一起指定。有关将游标与 WHERE CURRENT OF 一起使用的更多信息，请参阅 DECLARE。

输出表达式

在每次更新行后由 UPDATE 命令计算并返回的表达式。该表达式可以使用 表名 指定的表或 FROM 中列出的表(s) 的任何列名。编写 * 以返回所有列。

输出名称

用于返回列的名称。

输出

成功完成后，UPDATE命令会返回以下形式的命令标记

UPDATE count

*count是更新的行数，包括值未更改的匹配行。请注意，当更新被BEFORE UPDATE触发器禁止时，此数字可能小于与condition匹配的行数。如果count*为 0，则没有行被查询更新（这不被视为错误）。

如果UPDATE命令包含RETURNING子句，则结果将类似于SELECT语句，其中包含RETURNING列表中定义的列和值，这些列和值由命令更新的行计算得出。

注释

当存在FROM子句时，实质上会发生的是目标表将与*from_item*列表中提到的表联接，并且联接的每个输出行都表示目标表的更新操作。使用FROM时，应确保联接为每个要修改的行生成至多一个输出行。换句话说，目标行不应与其他表中的多行联接。如果联接，则仅使用其中一个联接行来更新目标行，但无法轻易预测将使用哪一个联接行。

由于这种不确定性，仅在子查询中引用其他表更安全，尽管通常比使用联接更难读且更慢。

对于分区表，更新行可能导致其不再满足包含分区的分区约束。在这种情况下，如果分区树中还有其他分区满足此行的分区约束，则此行将移至该分区。如果没有这样的分区，则会发生错误。在后台，行移动实际上是DELETE和INSERT操作。

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

请注意，虽然可以将行从本地分区移动到外键表分区（前提是外键数据包装器支持元组路由），但不能从外键表分区移动到另一个分区。

如果发现外键直接引用源分区的祖先（与UPDATE查询中提到的祖先不同），则尝试将行从一个分区移动到另一个分区将失败。

示例

将表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';

使用FROM子句语法增加管理 Acme Corporation 帐户的销售人员的销售数量

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

更新帐户表中的联系人姓名以匹配当前分配的销售人员

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

可以使用联接实现类似的结果

UPDATE accounts SET contact_first_name = first_name,
                    contact_last_name = last_name
  FROM employees WHERE employees.id = accounts.sales_person;

但是，如果employees.id不是唯一键，则第二个查询可能会产生意外结果，而如果有多个id匹配，则第一个查询保证会引发错误。此外，如果没有匹配的特定accounts.sales_person条目，第一个查询会将相应的名称字段设置为 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;
-- other operations
SAVEPOINT sp1;
INSERT INTO wines VALUES('Chateau Lafite 2003', '24');
-- Assume the above fails because of a unique key violation,
-- so now we issue these commands:
ROLLBACK TO sp1;
UPDATE wines SET stock = stock + 24 WHERE winename = 'Chateau Lafite 2003';
-- continue with other operations, and eventually
COMMIT;

更改表films中游标c_films当前所在行的kind列

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

兼容性

此命令符合SQL标准，但FROM和RETURNING子句是PostgreSQL扩展，并且可以使用WITH和UPDATE。

一些其他数据库系统提供了一个FROM选项，其中目标表应该再次列在FROM中。这不是PostgreSQL解释FROM的方式。在移植使用此扩展的应用程序时要小心。

根据标准，目标列名括号子列表的源值可以是任何产生正确列数的行值表达式。PostgreSQL仅允许源值是行构造器或子-SELECT。在行构造器情况下，可以将单个列的更新值指定为DEFAULT，但不能在子-SELECT中指定。