Skip to content

更新

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权限。你还必须对在*expressionscondition*中读取其值的任何列具有SELECT权限。

参数

with_query

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

table_name

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

alias

目标表的替代名称。提供别名时,它会完全隐藏表的实际名称。例如,给定 UPDATE foo AS fUPDATE 语句的其余部分必须将此表称为 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时,应确保联接为每个要修改的行生成至多一个输出行。换句话说,目标行不应与其他表中的多行联接。如果联接,则仅使用其中一个联接行来更新目标行,但无法轻易预测将使用哪一个联接行。

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

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

并发UPDATEDELETE操作可能会在移动行时导致序列化失败错误。假设会话 1 正在对分区键执行UPDATE操作,同时,此行对并发会话 2 可见,该会话对该行执行UPDATEDELETE操作。在这种情况下,会话 2 的UPDATEDELETE操作将检测到行移动并引发序列化失败错误(始终返回 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标准,但FROMRETURNING子句是PostgreSQL扩展,并且可以使用WITHUPDATE

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

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