逑美(皮肤健康咨询)
89.22M · 2025-09-29
在数据库操作中，修改已有数据是日常开发的高频需求——比如调整用户的联系信息、更新订单的支付状态、修正错误的统计数据。PostgreSQL通过UPDATE语句实现这一功能，它允许你精准定位需要修改的行，并灵活设置新值。
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 ] } [, ...] ]
我们可以将其简化为**“定位目标→设置新值→可选关联→过滤条件→返回结果”**的逻辑流程：
table_name指定要修改的表；SET子句定义列的新值；FROM子句关联其他表（如需跨表更新）；WHERE精准定位需要修改的行；RETURNING获取修改后的行数据（方便验证）。SET是UPDATE的核心，用于定义“要修改哪些列”和“修改成什么值”，支持3种常见写法：
最基础的用法：直接给某一列赋新值（可以是常量、表达式或函数结果）。
示例：将ID为1的用户邮箱修改为[email protected]：
UPDATE users
SET email = '[email protected]' -- 列名=新值
WHERE id = 1; -- 定位行
如果需要修改多个列，可以用括号+行构造器（ROW）或子查询批量赋值，避免重复写SET。
示例：同时修改用户的手机号和状态：
-- 方式1：行构造器
UPDATE users
SET (phone, status) = ROW('138xxxx1234', 'active') -- 多列对应多值
WHERE id = 1;
-- 方式2：子查询（适用于从其他表取数）
UPDATE users
SET (email, avatar) = (SELECT new_email, new_avatar FROM user_profiles WHERE user_id = 1)
WHERE id = 1;
如果列定义了默认值（比如created_at默认是当前时间），可以用DEFAULT将其重置为默认值。
示例：重置用户的“最后登录时间”为当前时间（假设last_login的默认值是CURRENT_TIMESTAMP）：
UPDATE users
SET last_login = DEFAULT -- 用DEFAULT触发默认值
WHERE id = 1;
WHERE是UPDATE的“安全锁”——没有WHERE的UPDATE会修改表中所有行，这几乎是开发中的“致命操作”！
WHERE支持所有布尔表达式，比如：
id = 1）；age BETWEEN 18 AND 30）；email LIKE '%@example.com'）；id IN (SELECT user_id FROM orders WHERE amount > 100)）。示例：修改“状态为未激活且30天未登录”的用户状态为“休眠”：
UPDATE users
SET status = 'dormant'
WHERE status = 'inactive' -- 条件1：未激活
AND last_login < CURRENT_DATE - INTERVAL '30 days'; -- 条件2：30天未登录
当需要根据其他表的数据修改当前表时，FROM子句就派上用场了——比如“根据订单表更新用户的总消费额”。
语法：UPDATE 目标表 SET 列=值 FROM 关联表 WHERE 关联条件。
示例：根据订单表orders更新用户表users的总消费额total_spent：
-- 用户表：users(id, total_spent)
-- 订单表：orders(user_id, amount)
UPDATE users u
SET total_spent = u.total_spent + o.amount -- 总消费增加订单金额
FROM orders o -- 关联订单表
WHERE u.id = o.user_id -- 关联条件：用户ID=订单的用户ID
AND o.id = 123; -- 仅更新订单123对应的用户
RETURNING是PostgreSQL的扩展功能，用于执行UPDATE后返回修改的行数据，方便验证或后续处理（比如返回给前端）。
语法：RETURNING *（返回所有列）或RETURNING 列1, 列2（返回指定列）。
示例：修改用户密码并返回修改后的关键信息：
UPDATE users
SET password = 'hashed_new_pass' -- 假设密码已哈希
WHERE id = 1
RETURNING id, email, updated_at; -- 返回ID、邮箱、更新时间
执行后会返回类似SELECT的结果集，包含修改后的行数据：
| id | updated_at | |
|---|---|---|
| 1 | [email protected] | 2024-05-20 14:30:00 |
当需要更新10万+行时，直接UPDATE会导致表级锁，阻塞其他并发请求。解决方案是分批更新——用CTE（Common Table Expression，公共表表达式）和LIMIT限制每次更新的行数。
原理：通过ctid（PostgreSQL的系统列，代表行的“物理地址”）快速定位行，每次更新小批量（比如1000行），减少锁的范围。
示例：分批修改“重试次数超过10次”的任务状态为“失败”：
WITH batch AS (
SELECT ctid FROM work_items -- 选择要更新的行的物理地址
WHERE status = 'active' AND retries > 10
LIMIT 1000 -- 每次更新1000行
)
UPDATE work_items
SET status = 'failed'
FROM batch
WHERE work_items.ctid = batch.ctid; -- 通过ctid关联
执行方式：重复运行此SQL，直到返回UPDATE 0（表示没有需要更新的行）。
WITH子句（也叫“CTE”）可以将复杂的更新逻辑拆分成多个“步骤”，提高可读性。比如“先计算每个用户的总订单金额，再更新用户表”。
示例：更新用户的总消费额（从订单表汇总）：
-- 第一步：计算每个用户的总订单金额（子查询）
WITH order_totals AS (
SELECT user_id, SUM(amount) AS total FROM orders GROUP BY user_id
)
-- 第二步：用子查询结果更新用户表
UPDATE users u
SET total_spent = ot.total -- 总消费=订单汇总金额
FROM order_totals ot -- 关联子查询结果
WHERE u.id = ot.user_id; -- 用户ID关联
在PL/pgSQL存储过程中，有时需要逐行处理数据（比如审核任务），这时可以用游标定位当前行，再用WHERE CURRENT OF修改。
示例：用游标修改“ Drama ”类型的电影为“ Dramatic ”：
BEGIN;
-- 1. 声明游标：指向所有Drama类型的电影
DECLARE c_films CURSOR FOR SELECT * FROM films WHERE kind = 'Drama';
-- 2. 打开游标
OPEN c_films;
-- 3. 逐行处理：修改当前游标指向的行
FETCH c_films INTO film_rec; -- 将当前行存入变量film_rec
UPDATE films SET kind = 'Dramatic' WHERE CURRENT OF c_films; -- 修改当前行
-- 4. 关闭游标
CLOSE c_films;
COMMIT;
场景：用户支付了订单（ID=123），需要做两件事：
表结构：
users：id（用户ID）、total_spent（总消费，默认0）；orders：id（订单ID）、user_id（用户ID）、amount（金额）、status（状态）。实现代码（用事务保证原子性）：
BEGIN;
-- 1. 更新订单状态（仅当状态是unpaid时）
UPDATE orders
SET status = 'paid'
WHERE id = 123 AND status = 'unpaid'; -- 避免重复更新
-- 2. 更新用户总消费（从订单表取金额）
UPDATE users u
SET total_spent = u.total_spent + (SELECT amount FROM orders WHERE id = 123)
WHERE u.id = (SELECT user_id FROM orders WHERE id = 123); -- 关联用户ID
COMMIT; -- 提交事务，确保两步都成功
users表中所有email结尾为@old.com的用户，将其email替换为@new.com，并返回修改后的id和new_email？答案：使用REPLACE函数和RETURNING子句：
UPDATE users
SET email = REPLACE(email, '@old.com', '@new.com') -- 替换字符串
WHERE email LIKE '%@old.com' -- 匹配结尾为@old.com的邮箱
RETURNING id, email AS new_email; -- 返回ID和新邮箱
解析：REPLACE(str, old_sub, new_sub)用于替换字符串中的子串；LIKE '%@old.com'匹配“以@old.com结尾”的邮箱；RETURNING返回修改后的结果。
答案：
LIMIT分批更新，每次更新小批量行（比如1000行）：WITH batch AS (
SELECT ctid FROM work_items -- 取行的物理地址
WHERE status = 'active' AND retries > 10
LIMIT 1000 -- 每次更新1000行
)
UPDATE work_items
SET status = 'failed'
FROM batch
WHERE work_items.ctid = batch.ctid; -- 关联物理地址
解析：ctid是PostgreSQL的系统列，代表行的物理位置，快速定位行；LIMIT限制每次更新的行数，减少锁的范围。
ERROR: syntax error at or near "FROM"FROM子句的位置错误（比如放在SET之前）。FROM必须在SET之后、WHERE之前，正确语法：
UPDATE users SET total_spent = 100 FROM orders WHERE users.id = orders.user_id;
UPDATE的语法顺序书写（参考官方文档的语法结构）。ERROR: duplicate key value violates unique constraintNOT EXISTS过滤重复值：
UPDATE users
SET email = '[email protected]'
WHERE id = 1
AND NOT EXISTS (SELECT 1 FROM users WHERE email = '[email protected]'); -- 确保新邮箱不存在
SELECT检查值是否唯一，或使用UPSERT（INSERT ... ON CONFLICT UPDATE）处理冲突。ERROR: column "users" of relation "users" does not existSET子句中添加了表名前缀（比如SET users.email = '[email protected]'），PostgreSQL不允许。UPDATE users SET email = '[email protected]' WHERE id = 1;
SET子句中的列名不需要表名——目标表已经在UPDATE后指定了。UPDATE语句语法与用法：www.postgresql.org/docs/17/sql…WITH子句）：www.postgresql.org/docs/17/que…
