更新
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
中指定。