CODEWITHSUNDEEP
phpmysql
Teosz
Teosz

Reputation: 51

MySQL Subtract from multiple rows in one query

I have the following table where parent_id, price, quantity and exp_date are primary fields.

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 3.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 10.00    | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 17.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

I want to subtract a distinct value from the overall quantity starting from the last modified row. Right now I have this query:

SET @remain = -19;
Update Stock_props SET quantity =
(SELECT IF((@remain := quantity+@remain) < 0,'0',@remain) as quantity)
WHERE parent_id = 2
ORDER BY last_modified DESC

This particular one is working because the value I subtract is more than the last row. It will output this:

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 0.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 0.00     | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 11.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

However if I want to subtract a smaller amount like 11 for example the result will be like this:

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 2.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 0.00     | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 19.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

instead of this:

+-----------+-------+----------+------------+---------------------+------------------+
| parent_id | price | quantity | exp_date   | last_modified       | last_modified_by |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 410   | 0.00     | 2016-07-30 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 300   | 2.00     | 0000-00-00 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+
| 2         | 540   | 17.00    | 2016-07-22 | 2016-07-22 18:14:34 | test             |
+-----------+-------+----------+------------+---------------------+------------------+

What am I missing here? Thank you in advance!

Upvotes: 2

Views: 937

Answers (1)

Teosz
Teosz

Reputation: 51

Alright, so if anyone stumble upon this question in the future the following queries worked perfectly:

ALTER TABLE Stock_props ADD helper numeric;
SET @remain = 11;
Update Stock_props SET quantity =
(SELECT IF(((@remain := quantity+@remain) < 0),0,@remain) as quantity),
helper = (SELECT IF((@remain>0), @remain:=0,@remain)as helper),
ORDER BY last_modified DESC;
ALTER TABLE Stock_props DROP helper;

The above works as following:

  1. add a new row to the table called helper
  2. set the value of quantity and @remain (latter is necessary because if it is negative we want to continue the subtraction)
  3. Overwrite the value of @remain based on its sign
  4. Drop the helper row

Upvotes: 3

Related Questions