从数据库中的多行中删除一个数量 [英] Removing a quantity from multiple rows in a database

问题描述

这是我的表结构:
table.id(自增；主键)
table.qty(可能的值:0 - 1000)

行:

<前>+----+-----+|身份证 |数量 |+----+-----+|1 |0 ||2 |5 ||3 |5 ||4 |10 |+----+-----+

我想将可用数量减少 12.意思是，新的结果应该是:
我可能应该指定所有这些数字都是任意的.它们可以是任何东西.

<前>+----+-----+|身份证 |数量 |+----+-----+|1 |0 ||2 |0 ||3 |0 ||4 |8 |+----+-----+

现在，我执行以下操作:

1. SELECT id, qty FROM table WHERE qty >0 限制 12 个

2. 过滤所有数据，找出每行要删除的数量

3. 使用 PDO 准备语句批量更新受影响的行:

   UPDATE `table` SET qty = ?哪里 id = ?`

我的问题:有没有办法在 MySQL 中做到这一切?

解决方案

SET @q = 12;更新`表`SET qty = CONCAT(GREATEST(qty - @q, 0), LEFT(@q := @q - LEAST(qty, @q), 0))按 id 排序；

在 sqlfiddle 上查看.

Here is my table structure:
table.id (auto increment; primary key)
table.qty ( possible values: 0 - 1000 )

Rows:

+----+-----+
| id | qty |
+----+-----+
|  1 |   0 |
|  2 |   5 |
|  3 |   5 |
|  4 |  10 |
+----+-----+

I want to decrease the quantity available by 12. Meaning, the new result should be:
Edit: I probably should have specified all these numbers are arbitrary. They could be anything.

+----+-----+
| id | qty |
+----+-----+
|  1 |   0 |
|  2 |   0 |
|  3 |   0 |
|  4 |   8 |
+----+-----+

Right now, I do the following:

1. SELECT id, qty FROM table WHERE qty > 0 LIMIT 12

2. Filter through all the data to figure out what qty to remove from each row

3. Use a PDO prepared statement to mass update the affected rows:

   UPDATE `table` SET qty = ? WHERE id = ?`
   

My question: Is there a way to do this all in MySQL?

解决方案

SET @q = 12;

UPDATE `table`
SET qty = CONCAT(GREATEST(qty - @q, 0), LEFT(@q := @q - LEAST(qty, @q), 0))
ORDER BY id;

See it on sqlfiddle.

这篇关于从数据库中的多行中删除一个数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！