Postgresql 中文操作指南

UPDATE

UPDATE — 更新表的行

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_item [, ...] ]
    [ WHERE condition | WHERE CURRENT OF cursor_name ]
    [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

Description

UPDATE 更改与条件相符的所有行中的指定列的值。仅需在 SET 子句中提及要修改的列；未明确修改的列保留其先前的值。

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

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

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

Parameters

  • with_query

    • WITH 子句允许您指定一个或多个子查询，这些子查询可以在 UPDATE 查询中通过名称引用。有关详细信息，请参见 Section 7.8 和 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_item

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

  • condition

    • 返回 boolean 类型值的一个表达式。只有此表达式返回 true 的行才会被更新。

  • cursor_name

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

  • output_expression

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

  • output_name

    • 用于返回列的名称。

Outputs

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

UPDATE count

count 是所更新行的数量，包括未更改值且匹配的行。请注意，这个数字可能小于匹配 condition 的行数，当更新被 BEFORE UPDATE 触发器禁止时会出现这种情况。如果 count 为 0，那么没有任何行被查询更新（这不是错误）。

如果 UPDATE 命令包含 RETURNING 从句，则结果会类似于 SELECT 语句，其中包含 RETURNING 列表中定义的列和值，并根据该命令更新的行来计算出来。

Notes

当 FROM 从句在场时，实际上会发生目标表连接到 from_item 列表中所提及的表，并且连接的每个输出行表示针对目标表的更新操作。使用 FROM 时，您应确保该连接针对要修改的每行最多产生一个输出行。换句话说，目标行不应该连接到其他表中的两行。如果确实发生了这种情况，那么将只用其中一个连接行来更新目标行，但是无法轻易预测将使用哪一个连接行。

由于这种不确定性，在子选择中引用其他表更为安全，尽管这种方式通常比使用连接更难理解且更慢。

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

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

请注意，虽然可以将行从本地分区移动到外来表分区（如果外来数据包装器支持元组路由），但无法从外来表分区移动到另一个分区。

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

Examples

在表 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;

更改当前指针 c_films 定位到的行中的表 films 的 kind 列：

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

Compatibility

此命令符合 SQL 标准，但 FROM 和 RETURNING 子句是 PostgreSQL 的扩展，而将 WITH 与 UPDATE 配合使用也是 PostgreSQL 的扩展。

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

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