How to deduct sold quantity from stock tables in MySQL

I have two mysql tables. The two tables with the sample data are as follows.

select * from stock;
+----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+
| stock_id | qty_type | qty    | item_id | stock_location_id | stock_type_id | purchase_id | created_date        |
+----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+
|       48 | v        |  44.00 |       1 |                 1 |             1 |          38 | 2022-05-16 14:27:19 |
|       49 | v        |   8.00 |     263 |                 1 |             1 |          38 | 2022-05-16 14:27:19 |
|       50 | a        |   6.00 |       1 |                 1 |             1 |          39 | 2022-05-16 14:30:04 |
|       51 | a        |   4.00 |     263 |                 1 |             1 |          39 | 2022-05-16 14:30:04 |
|       56 | a        |  28.00 |       1 |                 1 |             1 |          41 | 2022-05-16 14:51:59 |
|       57 | a        |  57.00 |     263 |                 1 |             1 |          41 | 2022-05-16 14:51:59 |
|       58 | a        |   6.00 |     264 |                 1 |             1 |          41 | 2022-05-16 14:51:59 |
|       59 | a        |  19.00 |     301 |                 1 |             1 |          41 | 2022-05-16 14:51:59 |
|       64 | a        |  15.00 |     263 |                 1 |             5 |           0 | 2022-05-18 17:23:37 |
|       65 | a        | 546.00 |     264 |                 1 |             5 |           0 | 2022-05-18 17:23:37 |
|       66 | a        |  15.00 |     263 |                 1 |             5 |           0 | 2022-05-18 17:24:21 |
|       67 | a        | 546.00 |     264 |                 1 |             5 |           0 | 2022-05-18 17:24:21 |
|       72 | v        |  20.00 |     720 |                 1 |             1 |          44 | 2022-05-24 09:24:43 |
|       73 | v        |   2.00 |     729 |                 1 |             1 |          44 | 2022-05-24 09:24:43 |
+----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+


select * from sales;
+----------+---------+----------+-----------+
| sales_id | item_id | quantity | basket_id |
+----------+---------+----------+-----------+
|        7 |       1 |    20.00 |         4 |
|        8 |     263 |     3.00 |         4 |
|        9 |       1 |     2.00 |         5 |
|       10 |     263 |     4.00 |         5 |
|       11 |     264 |     6.00 |         5 |
|       12 |     301 |     1.00 |         5 |
+----------+---------+----------+-----------+

By this I want to build up a update query. This should deduct the quantity of the items in the sales table from the stock table. If such a query can be created in mysql it should be updated in ascending order on the stock_id in the stock table.

If such an update query can be built, based on the data in the two tables above, I expect the result to be as follows.

select stock_id, qty_type, qty, item_id from stock where qty_type = 'a';
+----------+----------+--------+---------+
| stock_id | qty_type | qty    | item_id |
+----------+----------+--------+---------+
|       50 | a        |   0.00 |       1 | -- clear by sales
|       51 | a        |   0.00 |     263 | -- clear by sales
|       56 | a        |  12.00 |       1 | -- deduct qty by sales
|       57 | a        |  54.00 |     263 | -- deduct qty by sales
|       58 | a        |   0.00 |     264 | -- clear by sales
|       59 | a        |  18.00 |     301 | -- deduct qty by sales
|       64 | a        |  15.00 |     263 |
|       65 | a        | 546.00 |     264 |
|       66 | a        |  15.00 |     263 |
|       67 | a        | 546.00 |     264 |
+----------+----------+--------+---------+

Any help would be highly appreciated.

Answer

Here is my understanding of the problem:

According to your stock table, there are 06 quantity in hand of item_id #1

+----------+----------+-------+---------+
| stock_id | qty_type | qty   | item_id |
+----------+----------+-------+---------+
|       50 | a        |  6.00 |       1 |
+----------+----------+-------+---------+

Then again a new stock comes in and new entry is made in stock table. For example, another 28 quantity has come in, so the table would look like as follows:

+----------+----------+-------+---------+
| stock_id | qty_type | qty   | item_id |
+----------+----------+-------+---------+
|       50 | a        |  6.00 |       1 |
|       56 | a        | 28.00 |       1 | 
+----------+----------+-------+---------+

Now, there are 6 + 28 = 34 quantity in stock. According to your sales data, you have sold twice of item_id #1. As first time 20 items and next time 2 items. Since we have more quantity in stock than sold quantity, so we can do both sales.

As you mentioned in your question, now we have to adjust the sold items from stock in ascending order on the stock_id in the stock table. Of course here you are using the First In First Out (FIFO) method to maintain your stock. This method works in such a way that you have to clear stock which has come first.

Now it would not be a very simple SQL which can update the stock in hand, however this is my attempt.

INSERT INTO stock (stock_id, qty)
    SELECT stock_id
         , newqty
    FROM (
        SELECT
              stock_id
            , @sales := IF(@prev = k.item_id, @sales, sold) as sales
            , IF(qty <= @sales, 0, qty - @sales) as newqty
            , @sales := IF(@sales >= qty, @sales - qty, 0)
            , @prev := k.item_id as item
        FROM stock k
             JOIN (
                    SELECT item_id
                         , sum(quantity) as sold
                    FROM sales
                    GROUP BY item_id    
                  ) s ON k.item_id = s.item_id AND qty_type = 'a'
             JOIN (SELECT @prev:=null, @sales:=0) init
             ORDER BY k.item_id, stock_id
    ) calc
    ON DUPLICATE KEY UPDATE 
        qty = VALUES(qty);

My query can only be run once for a set of sales data as each time it reduces the stock and updates the stock table (as you requested). Not the best method as it means storing derived data.

The following is the result of running the above query one at a time each time data is added to the sales table.

Starting Stock

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id |
+----------+----------+-------+---------+-------------------+---------------+-------------+
|        1 | a        |  6.00 |       1 |                 1 |             5 |           0 |
|        2 | a        | 28.00 |       1 |                 1 |             5 |           0 |
+----------+----------+-------+---------+-------------------+---------------+-------------+

Result 01 – After 1st sale

select * from sales;
+----------+---------+----------+-----------+
| sales_id | item_id | quantity | basket_id |
+----------+---------+----------+-----------+
|        1 |       1 |    20.00 |         1 | 
+----------+---------+----------+-----------+

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | 
+----------+----------+-------+---------+-------------------+---------------+-------------+
|        1 | a        |  0.00 |       1 |                 1 |             5 |           0 | 
|        2 | a        | 14.00 |       1 |                 1 |             5 |           0 |
+----------+----------+-------+---------+-------------------+---------------+-------------+

Result 02 – After 2nd sale

select * from sales;
+----------+---------+----------+-----------+
| sales_id | item_id | quantity | basket_id |
+----------+---------+----------+-----------+
|        1 |       1 |    20.00 |         1 | 
|        2 |       1 |     2.00 |         2 | 
+----------+---------+----------+-----------+

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id |
+----------+----------+-------+---------+-------------------+---------------+-------------+
|        1 | a        |  0.00 |       1 |                 1 |             5 |           0 |
|        2 | a        | 12.00 |       1 |                 1 |             5 |           0 |
+----------+----------+-------+---------+-------------------+---------------+-------------+