如何在单个SQL查询中基于列和变量的计算更新列?

Question

我有一个mySQL表，表示"产品";并有3个栏目:"产品编号"、"仓库";数量和"商店"数量，并希望在单个查询中减去订购数量，首先从仓库，然后从商店，如果需要更多的数量，则在仓库中低于'0'。

的例子:

INSERT INTO `product` (`product_id`, `quantity_warehouse`, `quantity_shop`, ) VALUES
(1001, 5, 3),
(1002, 5, 3),
(1003, 5, 3)

1. 如果我订购3件产品1001，那么3将从仓库中扣除仓库=2，商店=3
2. 如果我订购7件产品1002，那么5件将从仓库中扣除and 2 from shop so: warehouse=0, shop=1
3. 如果我订购9件产品1003，那么6件将从仓库中扣除仓库=-1，商店=0

我所做的到目前为止，但不工作和更新'0':

UPDATE
products
SET 
quantity_warehouse= CASE
WHEN ((quantity_warehouse+quantity_shop)<'#ordered_quantity') THEN quantity_warehouse=(quantity_warehouse-('#ordered_quantity'-quantity_shop))
WHEN (quantity_warehouse<'#ordered_quantity') THEN quantity_warehouse=0
ELSE quantity_warehouse=(quantity_warehouse-'#ordered_quantity') END,
quantity_shop   = CASE
WHEN ((quantity_warehouse+quantity_shop)<'#ordered_quantity') THEN quantity_shop=0
WHEN (quantity_shop<('#ordered_quantity'-quantity_warehouse)) THEN quantity_shop=0
ELSE quantity_shop=(quantity_shop-('#ordered_quantity'-quantity_warehouse)) END
WHERE
product_id = '1001'

伪代码示例:

if (warehouse<order){
if (shop<(order-warehouse)){
shop=0;
}else{
shop=shop-(order-warehouse);
}
warehouse=0;
}else{
warehouse=warehouse-order;
}

查询应该是什么样子?都是"case"。合适?

Answer 1

您将值设置为比较的结果

好的，那花了我一点时间。

所以当你第一次覆盖quantity_warehouse时，mysql获取新值并尝试更新第二个

所以我们需要保存"old"值并使用它们

Schema (MySQL v8.0)

CREATE TABLE `products` (`product_id` int ,`quantity_warehouse` int, `quantity_shop` int );
INSERT INTO `products` (`product_id`, `quantity_warehouse`, `quantity_shop` ) VALUES
(1001, 5, 3),
(1002, 5, 3),
(1003, 5, 3);
UPDATE
products CROSS JOIN (SELECT (quantity_warehouse+quantity_shop) sumw,quantity_warehouse  as qw FROM products WHERE  `product_id` = '1001') t1
SET 
quantity_warehouse= CASE
WHEN (t1.sumw<'7') THEN 
(quantity_warehouse-('7' + 0 -quantity_shop))
WHEN (quantity_warehouse<'7') THEN 0
ELSE (quantity_warehouse-'7') END,
quantity_shop   = CASE
WHEN ((t1.sumw)<'7') THEN 0
WHEN (quantity_shop<('7' -t1.qw)) THEN 0
ELSE (quantity_shop-('7'  -t1.qw)) END
WHERE
product_id = '1001'

---

查询# 1

SELECT * FROM products;

tbody> <<tr>

product_id	quantity_warehouse	quantity_shop
1001	0	1
1002	5	3
1003	5	3