Teosz Teosz - 4 months ago 9
MySQL Question

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!

Answer

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