跳到主要内容

更新数据

修改表中已有的行使用 UPDATE。一条 UPDATE 通常包含三个问题：修改哪张表、哪些列采用什么新值、哪些行需要修改。

本文以通用 SQL 为主，示例采用 PostgreSQL 可执行的语法。多表更新、限制更新行数和返回修改结果等能力存在数据库方言差异，会单独说明。

UPDATE 的基本结构​

UPDATE table_name
SET column_1 = value_1,
    column_2 = value_2
WHERE condition;

继续使用“插入数据”中的商品表 products。把编号为 BK-001 的商品价格修改为 62.00：

UPDATE products
SET price = 62.00
WHERE sku = 'BK-001';

数据库会先找出满足 WHERE 条件的行，再为这些行计算并写入新值。语句的执行结果通常还会包含受影响行数，应用程序应检查它是否符合预期。

精确选择要更新的行​

WHERE 决定更新范围，也是 UPDATE 中最需要谨慎检查的部分。

UPDATE products
SET status = 'off_sale'
WHERE stock = 0;

这会修改所有库存为 0 的商品。若省略 WHERE，则会更新表中的每一行：

-- 所有商品都会被修改
UPDATE products
SET status = 'off_sale';

全表更新有时是有意的数据迁移，但在日常业务操作中通常意味着条件遗漏。执行重要更新前，可以先用同样的条件查询目标行：

SELECT product_id, sku, product_name, status
FROM products
WHERE stock = 0
ORDER BY product_id;

预览只能帮助检查条件，不能保证预览和更新之间数据不变。若两步必须针对同一批数据，应把它们放进合适的事务，并根据并发要求使用锁或更高的隔离级别。

优先使用稳定标识​

商品名称可能重复或被修改，因此按唯一的 product_id 或 sku 更新通常更可靠：

UPDATE products
SET product_name = 'SQL 入门（第 2 版）'
WHERE product_id = 101;

如果业务要求只修改一行，受影响行数不是 1 时不应直接忽略：0 可能表示记录不存在或条件已经过期，大于 1 则通常说明条件不够精确。

不同数据库和驱动对“值没有实际变化的匹配行”是否计入受影响行数可能有差异。应用逻辑依赖该数字时，应核对目标数据库与驱动的定义。

正确判断 NULL​

不能用 = NULL 寻找空值，应使用 IS NULL：

UPDATE products
SET description = '暂无简介'
WHERE description IS NULL;

NULL 表示未知或缺失，参与普通比较时不会得到 TRUE。

同时更新多个列​

在 SET 中用逗号分隔多个赋值：

UPDATE products
SET price = 68.00,
    stock = 20,
    status = 'on_sale'
WHERE sku = 'BK-001';

每个目标列在同一条语句中最多赋值一次。列值必须通过数据类型、NOT NULL、CHECK、唯一键和外键等约束。

PostgreSQL 还支持行赋值写法：

UPDATE products
SET (price, stock, status) = (68.00, 20, 'on_sale')
WHERE sku = 'BK-001';

这种写法更紧凑，但并非所有数据库都支持。跨数据库项目优先使用逐列赋值。

根据原值计算新值​

SET 右侧可以使用当前行的列和表达式。所有在售商品价格提高 10%：

UPDATE products
SET price = ROUND(price * 1.10, 2)
WHERE status = 'on_sale';

减少库存时不要先在应用中读取旧值，再计算并写回；并发请求可能读取到同一个旧值，造成更新丢失。可以让数据库原子地完成判断和计算：

UPDATE products
SET stock = stock - 1
WHERE product_id = 101
  AND stock >= 1;

若受影响行数为 0，说明商品不存在或库存不足。条件判断与减库存属于同一条语句，其他事务不能在两者之间插入一次写操作。

表达式中包含 NULL 时要注意传播规则。例如 price * 1.10 在 price 为 NULL 时仍为 NULL。确实需要替代值时可以显式使用 COALESCE：

UPDATE products
SET stock = COALESCE(stock, 0) + 5
WHERE product_id = 101;

不要用 COALESCE 随意掩盖本应由 NOT NULL 约束阻止的数据问题。

使用 DEFAULT 和条件表达式​

把列恢复为它当前定义的默认值：

UPDATE products
SET status = DEFAULT
WHERE sku = 'BK-001';

这不是恢复该行曾经的初始值，而是重新计算列的默认表达式。若列没有显式默认值，结果通常为 NULL，并且仍需通过列约束。

需要按行选择不同结果时可以使用 CASE：

UPDATE products
SET status = CASE
  WHEN stock = 0 THEN 'off_sale'
  ELSE 'on_sale'
END
WHERE status IN ('on_sale', 'off_sale');

单条集合式更新通常比应用程序逐行循环执行 SQL 更清晰、更高效，也更容易保持事务一致性。

根据其他表更新​

通用性较好的方式是使用相关子查询。下面根据 product_price_changes 调价表更新商品价格：

UPDATE products AS p
SET price = (
  SELECT c.new_price
  FROM product_price_changes AS c
  WHERE c.product_id = p.product_id
)
WHERE EXISTS (
  SELECT 1
  FROM product_price_changes AS c
  WHERE c.product_id = p.product_id
);

EXISTS 避免把没有调价记录的商品更新为 NULL。子查询对每个目标商品必须最多返回一行，否则语句会失败；应使用唯一约束表达这一规则，不要依赖碰巧没有重复数据。

PostgreSQL 支持 UPDATE ... FROM：

UPDATE products AS p
SET price = c.new_price
FROM product_price_changes AS c
WHERE c.product_id = p.product_id;

如果一个目标商品匹配多条调价记录，PostgreSQL 只会选择其中一条，具体选择哪条并不确定。因此连接键必须保证每个目标行至多匹配一行。

MySQL 常用 UPDATE ... JOIN，SQL Server 也支持自己的 UPDATE ... FROM 形式。多表更新不是完全通用的 SQL，迁移前应核对语法和多重匹配行为。

防止覆盖别人的修改​

“先查询、用户编辑、再更新”期间，其他请求可能已经修改了同一行。若最后一次提交直接覆盖整行，就会发生丢失更新。

一种常见做法是增加版本列并进行乐观并发控制：

UPDATE products
SET price = 72.00,
    version = version + 1
WHERE product_id = 101
  AND version = 7;

应用先前读取到 version = 7。若受影响行数为 0，说明记录不存在或已被其他事务改动，应用应重新读取并提示冲突，而不是无条件重试覆盖。

需要连续执行“读取、判断、更新”的复杂逻辑时，可以在事务中使用目标数据库提供的行锁，例如 PostgreSQL 的 SELECT ... FOR UPDATE。锁会降低并发度，并可能发生死锁，因此事务应尽量短，并让不同流程按一致顺序访问资源。

约束、触发器和生成列​

更新仍会执行数据库的数据规则：

规则更新时的影响
NOT NULL新值不能为 NULL
CHECK新行状态必须满足检查表达式
UNIQUE新值不能与其他行冲突
FOREIGN KEY引用值必须存在，或触发配置的级联动作
生成列通常由数据库根据其他列重新计算
触发器可能改写新值、拒绝操作或写入审计记录

默认值不会因为普通 UPDATE 自动重新执行，created_at 也不会自动变成当前时间。需要维护 updated_at 时，应由更新语句、应用程序或触发器明确负责。

更新唯一键或父表主键时，还要检查外键的 ON UPDATE 动作。大范围级联可能锁定很多行并产生大量日志。

事务与大批量更新​

多条相关更新应放在一个事务中：

BEGIN;

UPDATE products
SET stock = stock - 2
WHERE product_id = 101
  AND stock >= 2;

UPDATE warehouses
SET reserved_stock = reserved_stock + 2
WHERE warehouse_id = 10;

COMMIT;

任一步失败时应 ROLLBACK，避免只完成一半。正式执行高风险更新前，可以在测试环境验证；在支持事务性 DML 的数据库中，也可先在事务内检查结果再决定提交，但不要把长时间人工确认的事务留在生产环境中占用锁。

一次修改大量行可能带来长事务、锁等待、事务日志膨胀、复制延迟和表膨胀。必要时按稳定且有索引的键分批处理，并记录批次边界。不同数据库限制更新行数的语法不同，不能假设 UPDATE ... LIMIT 可移植。

索引能加快 WHERE 定位，但被更新的索引列也会增加维护成本。应通过执行计划和真实数据量评估，而不是给所有可更新列都建索引。

参数化与权限​

应用程序应使用驱动的参数绑定：

UPDATE products
SET price = :new_price
WHERE product_id = :product_id;

占位符可能是 ?、$1、:name 或 @name，以驱动为准。不要把用户输入拼进 SQL 字符串。表名和列名通常不能作为普通参数绑定，动态对象名应通过服务端白名单选择。

数据库账户只应拥有所需表和列的权限。读取 WHERE、表达式或关联表中的列还可能需要相应的 SELECT 权限；行级安全策略也可能进一步限制可见和可更新的行。

更新数据检查清单​

  1. 是否用 SELECT 验证了 WHERE 会匹配哪些行；
  2. 条件是否使用主键、唯一键或其他稳定标识；
  3. 是否正确使用 IS NULL，并为 AND、OR 添加了清晰的括号；
  4. 新值是否会违反非空、检查、唯一键或外键约束；
  5. 根据其他表更新时，每个目标行是否最多匹配一行；
  6. 是否检查受影响行数，并正确处理 0 行和超出预期的行数；
  7. 并发写入是否需要原子条件、版本号或行锁；
  8. 多条相关操作是否应处于同一个事务；
  9. 大批量更新是否需要分批，并评估锁、日志和复制影响；
  10. 应用代码是否使用参数化查询，并确认了目标数据库的方言。

安全更新的核心不是记住更多语法，而是让目标范围、计算规则和并发结果都可以被验证。