如何编写单条MySQL查询按规则基于库存列与订购量更新库存字段
MySQL 优先扣减仓库库存的单条更新语句实现
你原有语句出错的核心原因是:MySQL 同一 UPDATE 语句中,所有 SET 赋值的计算都基于字段更新前的原始值,且多个字段的更新是并行的,不会使用同一条语句中其他字段更新后的值,你原逻辑依赖了更新过程中 quantity_warehouse 的变化,自然会得到错误结果。
CASE 语句完全适用于该场景,只要调整计算逻辑,全部用原始库存值推导两个字段的更新结果即可。
正确更新语句
UPDATE products SET quantity_warehouse = CASE -- 仓库库存足够,直接扣减订购量 WHEN quantity_warehouse >= #ordered_quantity THEN quantity_warehouse - #ordered_quantity -- 总库存足够,仓库扣完为0 WHEN quantity_warehouse + quantity_shop >= #ordered_quantity THEN 0 -- 总库存不足,扣完全部门店后剩余部分扣仓库,允许为负 ELSE quantity_warehouse + quantity_shop - #ordered_quantity END, quantity_shop = CASE -- 仓库库存足够,门店库存不变 WHEN quantity_warehouse >= #ordered_quantity THEN quantity_shop -- 总库存足够,扣减超出仓库的部分 WHEN quantity_warehouse + quantity_shop >= #ordered_quantity THEN quantity_shop - (#ordered_quantity - quantity_warehouse) -- 总库存不足,门店扣完为0 ELSE 0 END WHERE product_id = '1001';
效果验证
完全匹配你给出的3个扣减示例:
- 订购3件ID为1001的商品:仓库库存5>=3,更新后仓库为2、门店为3
- 订购7件ID为1002的商品:仓库5<7、总库存8>=7,更新后仓库为0、门店为3-(7-5)=1
- 订购9件ID为1003的商品:总库存8<9,更新后仓库为5+3-9=-1、门店为0
注意事项
业务使用时建议对 #ordered_quantity 做正数校验,避免负订购量导致库存异常增加的问题。
内容的提问来源于stack exchange,提问作者WhiteHatMan
相关产品推荐
相关产品推荐

