PostgreSQL / 系统课时

使用表达式更新字段

使用表达式更新字段

本课解决根据现有数据计算新值的问题。完成后,你能在 SET 中使用算术运算、函数和 CASE,一次完成价格、库存与状态的联动更新,并通过查询结果确认计算是否正确。

学习目标

  • 基于字段旧值计算新值
  • 使用函数控制更新边界
  • 使用 CASE 生成条件结果

在 SET 中编写表达式

SET 右侧可以引用当前行原有字段。PostgreSQL 会基于更新前的行值计算各个赋值表达式,不应把同一条语句理解为从左到右逐项覆盖。

例如,商品价格上调 10%,库存减少 3 件,同时根据扣减后的库存调整状态。这三个字段可以在同一条 UPDATE 中完成,但必须明确每个表达式读取的是更新前的字段值。

以下 SQL 可在 PostgreSQL 中完整执行。示例先创建独立的商品表并写入三条测试数据,再只更新图书类商品。

sql
DROP TABLE IF EXISTS lesson_goods;

CREATE TABLE lesson_goods (
    id integer PRIMARY KEY,
    name varchar(100) NOT NULL,
    category varchar(20) NOT NULL,
    price numeric(10, 2) NOT NULL,
    stock integer NOT NULL,
    stock_level varchar(20) NOT NULL
);

INSERT INTO lesson_goods (id, name, category, price, stock, stock_level)
VALUES
    (1, 'PostgreSQL Guide', 'book', 80.00, 12, 'normal'),
    (2, 'SQL Handbook', 'book', 50.00, 2, 'low'),
    (3, 'Mouse', 'device', 100.00, 10, 'normal');

-- 只更新图书,保留设备类商品作为范围验证
UPDATE lesson_goods
SET
    price = round(price * 1.10, 2),
    stock = greatest(stock - 3, 0),
    -- CASE 读取旧库存,因此显式计算扣减后的数量
    stock_level = CASE
        WHEN stock - 3 <= 0 THEN 'out'
        WHEN stock - 3 < 10 THEN 'low'
        ELSE 'normal'
    END
WHERE category = 'book';

-- 按主键排序,便于逐行核对更新结果
SELECT id, name, price, stock, stock_level
FROM lesson_goods
ORDER BY id;

预期结果

text
 id |       name       | price  | stock | stock_level
----+------------------+--------+-------+------------
  1 | PostgreSQL Guide |  88.00 |     9 | low
  2 | SQL Handbook     |  55.00 |     0 | out
  3 | Mouse            | 100.00 |    10 | normal

第一条图书记录的价格从 80.00 变为 88.00,库存从 12 变为 9,状态变为 low。第二条图书记录只有 2 件库存,扣减后被限制为 0,状态变为 out。设备类商品不满足 WHERE 条件,因此保持原值。

三类常用表达式

算术表达式适合累计数量、折扣和涨价;greatest、least 等函数可限制上下界;CASE 可按条件写入不同状态。这些表达式都可以直接出现在 SET 的赋值右侧。

  • 算术运算:price * 1.10 表示在原价基础上增加 10%,round(price * 1.10, 2) 将结果保留两位小数,适合示例中的金额字段。
  • 边界函数:greatest(stock - 3, 0) 取扣减结果与 0 中的较大值,防止库存变成负数;需要设置数量上限时,可使用 least。
  • 条件表达式:CASE 按 WHEN 的顺序判断条件,采用第一个匹配分支的结果;所有条件均不满足时使用 ELSE。

这里的库存等级采用明确的业务规则:扣减后库存小于等于 0 为 out,大于 0 且小于 10 为 low,其余为 normal。条件顺序不能随意颠倒,否则可能让缺货商品先匹配到低库存分支。

注意旧值语义

示例中的 stock_level 判断使用更新前的 stock,所以表达式写成 stock - 3,与新库存保持一致。不能认为前面已经执行了 stock = greatest(stock - 3, 0),后面的 CASE 就能直接读取更新后的库存。

同一条 UPDATE 中,各字段的赋值表达式基于同一行的更新前数据进行求值。如果状态规则依赖新计算结果,需要在相关表达式中重复计算,或在更复杂的场景中先组织好待更新的数据。

对于这段 SQL,stock - 3 <= 0 与 greatest(stock - 3, 0) = 0 在库存非空的前提下具有相同的缺货判定效果。这样即使原库存只有 2 件,实际写入的库存和状态也不会相互矛盾。

验证更新范围与业务边界

执行完成后,应检查三个方面:图书价格是否正确上调、库存是否符合下限约束,以及不属于图书类别的记录是否保持不变。本例最后的 SELECT 就是验证查询,三行结果可以分别覆盖正常扣减、库存归零和未命中更新条件三种情况。

执行实际业务更新前,还要注意以下边界:

  • 限制更新范围:WHERE category = 'book' 只更新图书。若遗漏 WHERE,表中的所有商品都会被更新。
  • 区分库存截断和库存校验:greatest(stock - 3, 0) 只保证结果不小于 0,并不表示原库存足够扣减 3 件。如果业务要求库存必须充足,应增加 AND stock >= 3,并检查实际更新行数;此时示例中的第二条图书记录将不再被更新。
  • 防止计算规则不一致:复杂更新可以先用 SELECT 预览计算值,确保预览与正式 UPDATE 使用相同的表达式和条件。

另外,numeric(10, 2) 适合保存本例的两位小数价格,但计算后的值仍必须处于字段允许的数值范围内,否则 PostgreSQL 会报错,而不是自动写入超出精度限制的金额。

当多个字段需要根据同一行的原始数据联动变化时,可以使用一条带表达式的 UPDATE 完成更新。只要明确旧值语义、更新范围与业务约束,就能避免字段计算结果不一致或误更新其他记录。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

使用表达式更新字段

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通