MySQL update query to automatically Deduct quantity sold from quantity in the stock table

[thara]

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.

[Barand]

Why have you contrived a stock table with multiple rows per item?

What is the significance of qty_type?

[thara]

Sir, there are mainly 3 type of stocks (Purchase, Donation and Opening ). That is why "stock_type_id" there in stock table. "purchase_id" also there to identify qty by purchase invoices. Here I have to make some reports based on purchase invoices and stock types. That is the reason for looking for a solution of the above kind.

qty_type is a flag for actual and virtual quantity of the table.
'a'=actucal (received qty)
'v'=virtual (pindng and ordered)

Edited June 4, 2022 by thara

[mac_gyver]

if you are doing this for real, you would NOT actually update any values. you would just use a UNION query to calculate the current total, between the stock and the sales table, for each item_id you are interested in.

actually using the oldest inventory first, is a fulfilment issue, not a stock/inventory quantity problem.

[thara]

12 hours ago, mac_gyver said:

if you are doing this for real, you would NOT actually update any values. you would just use a UNION query to calculate the current total, between the stock and the sales table, for each item_id you are interested in.

actually using the oldest inventory first, is a fulfilment issue, not a stock/inventory quantity problem.

Thanks for your reply.

As you suggested a UNION query, I currently use a query as follows when I need to obtain current stock and sales quantity.

SELECT i.item_id
     , i.item_name
     , i.sku
     , round(coalesce(current_actual_stock,0), 0) as actual_stock
     , round(coalesce(current_virtual_stock,0), 0) as virtual_stock
     , round(coalesce(sum(sl.quantity),0), 0) as item_sold
  FROM item i
      LEFT JOIN (
         SELECT item_id, qty_type, sum(qty) AS current_actual_stock
           FROM stock
          WHERE qty_type = 'a'
       GROUP BY item_id
                ) s1 USING (item_id)             
      LEFT JOIN (
         SELECT item_id, qty_type, sum(qty) AS current_virtual_stock
           FROM stock
          WHERE qty_type = 'v'
       GROUP BY item_id
                ) s2 USING (item_id)
      LEFT JOIN sales sl USING (item_id)
  GROUP BY item_id
  ORDER BY item_id

In the previous occation, my stock table was structured as one row per item. Then the stock update can be done without any problems. But with the advent of the stock type, the table structure had to be changed as above.

May I know from you here, what is the most suitable method for this?

Yours comments and suggestions are greatly appreciated.

Thank you.

[Barand]

try

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);

 

[thara]

Thank you Sir,

Do I need to assign values to @prev and @sales variables when it is executing? or can I execute the query without doing anything to your query?

I tried both methods. In both cases the first execution is done correctly, but the qty decrease is not correct every time the next execution is done.

 

 

[Barand]

18 minutes ago, thara said:

Do I need to assign values to @prev and @sales variables

That is done in the query.

 

19 minutes ago, thara said:

the qty decrease is not correct every time the next execution is done

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. I only did it as an intellectual exercise, picking up the gauntlet that you threw down.

As your select query only reads data it can be run as often you want. Using your purchase order table as a stock table is not one of your best ideas. Maintain that table and the sales table then query the two, as you have done, to get the current stock position.

[thara]

1 hour ago, Barand said:

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)

 

Sir, as you say, the above query was run only once. Once meant, sales data was inserted into the sales table every time a sale took place. Whenever that happens I want to reduce the stock by running the above query once.

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

Initial Stock

select * from stock;
+----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty    | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 100.00 |       2 |                 1 |             5 |           0 | 2022-06-06 11:22:58 | 2022-06-06 11:22:58 |
|        2 | a        | 100.00 |       1 |                 1 |             5 |           0 | 2022-06-06 11:22:58 | 2022-06-06 11:22:58 |
+----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Result 01 - After 1st sale

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

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 97.00 |       2 |                 1 |             5 |           0 | 2022-06-06 17:27:10 | 2022-06-06 17:28:57 |
|        2 | a        | 98.00 |       1 |                 1 |             5 |           0 | 2022-06-06 17:27:10 | 2022-06-06 17:28:57 |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

 

Result 02 - After 2nd sale

select * from sales;
+----------+---------+----------+-----------+
| sales_id | item_id | quantity | basket_id |
+----------+---------+----------+-----------+
|        1 |       1 |     2.00 |        26 |
|        2 |       2 |     3.00 |        26 |
|        3 |       1 |    10.00 |        27 | -- from 2nd sale
+----------+---------+----------+-----------+

 select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 94.00 |       2 |                 1 |             5 |           0 | 2022-06-06 17:27:10 | 2022-06-06 17:30:01 |
|        2 | a        | 86.00 |       1 |                 1 |             5 |           0 | 2022-06-06 17:27:10 | 2022-06-06 17:30:01 |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

 

Result 03 - After 3rd sale

select * from sales;
+----------+---------+----------+-----------+
| sales_id | item_id | quantity | basket_id |
+----------+---------+----------+-----------+
|        1 |       1 |     2.00 |        26 |
|        2 |       2 |     3.00 |        26 |
|        3 |       1 |    10.00 |        27 |
|        4 |       1 |     5.00 |        28 | -- 3rd sale
|        5 |       2 |     4.00 |        28 | -- 3rd sale
+----------+---------+----------+-----------+

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 87.00 |       2 |                 1 |             5 |           0 | 2022-06-06 17:27:10 | 2022-06-06 17:32:47 |
|        2 | a        | 69.00 |       1 |                 1 |             5 |           0 | 2022-06-06 17:27:10 | 2022-06-06 17:32:47 |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

 

Edited June 6, 2022 by thara

[Barand]

Taking a look at just item_id #1.

• Starting stock  : 100
• First run sales : 2
• Stock now : 98
• Second run sales : 12 (2 + 10)
• Stock now : 86
• Third run sales : 17 (2 + 10 + 5)
• Stock now : 69.

Each time you run it you are subtracting sales that have already been subtracted. Each run needs to process only sales since the last stock update. I woud have done this in the query but your sales data contains no date info to make it possible.

[thara]

Hello Sir,

My sales table is an one to many relationship with basket table. So there is a datetime column in the basket table to keep the date information for each sale.

Eg:
 

select basket_id, sale_time from basket;

+-----------+---------------------+
| basket_id | sale_time           |
+-----------+---------------------+
|        26 | 2022-06-06 17:28:05 |
|        27 | 2022-06-06 17:29:39 |
|        28 | 2022-06-06 17:32:03 |
+-----------+---------------------+

So, Can we use this column for the update query?

[Barand]

Of course you can. Just process those sales where basket.sale_time > latest stock.updated_date.

PS this assumes that that the updated_date is only updated when the stock levels are updated and not when a purchase is added.

[thara]

Sir, I modified the query as below, now query is executing but not updating..

INSERT INTO stock (stock_id, qty, updated_date)
    SELECT stock_id
         , newqty
         , NOW() as updatedDate
    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 sl
                      JOIN stock s USING (item_id)
                      JOIN basket b ON b.basket_id = sl.basket_id
                                    AND b.sale_time > coalesce(s.updated_date, NOW())      
                    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),
        updated_date = VALUES(updated_date);

Also I changed "updated_date" column in stock table. now its not update when a purchase is added. 

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+
|        1 | a        | 50.00 |       2 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | NULL         |
|        2 | a        | 50.00 |       1 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | NULL         |
|        5 | a        | 50.00 |       1 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | NULL         |
|        6 | a        | 50.00 |       2 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | NULL         |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+

 

[Barand]

You can't join to the stock table in the subquery for the total sales. If you have, say, 3 records for an item then you will treble the sales total for that item.

Something like this..

SELECT item_id
     ,  sum(quantity) as sold
FROM sales sl
     JOIN basket b ON b.basket_id = sl.basket_id
                  AND b.sale_time > (SELECT MAX(updated_date) FROM stock)      
GROUP BY item_id

 

[thara]

Sir, this is how my query now. But still I can not get the correct updation.

INSERT INTO stock (stock_id, qty, updated_date)
    SELECT stock_id
         , newqty
         , NOW() as updatedDate
    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 sl
                      JOIN stock s USING (item_id)
                      JOIN basket b ON b.basket_id = sl.basket_id
                                    AND b.sale_time > (SELECT COALESCE(MAX(updated_date),0) FROM stock)       
                    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),
        updated_date = VALUES(updated_date);

 

[Barand]

Working OK my end (unless I missed something), perhaps it's your data.

Send dumps of stock, sales and basket that you are testing with.

[thara]

Sir, This is few result set

Starting Stock

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+
|        1 | a        | 50.00 |       2 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | NULL         |
|        2 | a        | 50.00 |       1 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | NULL         |
|        5 | a        | 50.00 |       1 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | NULL         |
|        6 | a        | 50.00 |       2 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | NULL         |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+

Result 01 - After 1st sale

select s.*, sale_time  from sales s join basket b using(basket_id);
+----------+---------+----------+-----------+---------------------+
| sales_id | item_id | quantity | basket_id | sale_time           |
+----------+---------+----------+-----------+---------------------+
|        1 |       1 |     5.00 |        36 | 2022-06-06 23:29:28 |
|        2 |       2 |     4.00 |        36 | 2022-06-06 23:29:28 |
+----------+---------+----------+-----------+---------------------+

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 42.00 |       2 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 |
|        2 | a        | 40.00 |       1 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 |
|        5 | a        | 50.00 |       1 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 |
|        6 | a        | 50.00 |       2 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Result 02 - After 2nd sale

select s.*, sale_time  from sales s join basket b using(basket_id);
+----------+---------+----------+-----------+---------------------+
| sales_id | item_id | quantity | basket_id | sale_time           |
+----------+---------+----------+-----------+---------------------+
|        1 |       1 |     5.00 |        36 | 2022-06-06 23:29:28 |
|        2 |       2 |     4.00 |        36 | 2022-06-06 23:29:28 |
|        3 |       1 |     1.00 |        37 | 2022-06-06 23:35:05 |
+----------+---------+----------+-----------+---------------------+

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 42.00 |       2 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 |
|        2 | a        | 38.00 |       1 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | 2022-06-06 23:35:19 |
|        5 | a        | 50.00 |       1 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | 2022-06-06 23:35:19 |
|        6 | a        | 50.00 |       2 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Result 03 - After 3rd sale
 

select s.*, sale_time  from sales s join basket b using(basket_id);
+----------+---------+----------+-----------+---------------------+
| sales_id | item_id | quantity | basket_id | sale_time           |
+----------+---------+----------+-----------+---------------------+
|        1 |       1 |     5.00 |        36 | 2022-06-06 23:29:28 |
|        2 |       2 |     4.00 |        36 | 2022-06-06 23:29:28 |
|        3 |       1 |     1.00 |        37 | 2022-06-06 23:35:05 |
|        4 |       1 |     1.00 |        38 | 2022-06-06 23:48:06 |
+----------+---------+----------+-----------+---------------------+

select * from stock;
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty   | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 42.00 |       2 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | 2022-06-06 23:33:49 |
|        2 | a        | 36.00 |       1 |                 1 |             5 |           0 | 2022-06-06 20:12:39 | 2022-06-06 23:48:49 |
|        5 | a        | 50.00 |       1 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | 2022-06-06 23:48:49 |
|        6 | a        | 50.00 |       2 |                 1 |             1 |          48 | 2022-06-06 20:15:18 | 2022-06-06 23:33:49 |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

 

[Barand]

Forget the above. I just noticed you have updated the subquery as I told you!

That could be the problem.

[thara]

Sir, you mean this?

INSERT INTO stock (stock_id, qty, updated_date)
    SELECT stock_id
         , newqty
         , NOW() as updatedDate
    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 sl
                      JOIN basket b ON b.basket_id = sl.basket_id
                                    AND b.sale_time > (SELECT COALESCE(MAX(updated_date),0) FROM stock)       
                    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),
        updated_date = VALUES(updated_date);

 

[Barand]

Now it looks like you read my post.

Have you tried it?

[thara]

Yes sir, its my mistake. now it's working nicely.

One thing, the updated_date updates all records related to item_id.

[Barand]

Just now, thara said:

One thing, the updated_date updates all records related to item_id.

They are updated - it's just that the qty value hasn't changed in all of them if the sales were fulfilled by earlier stock records.

[thara]

Thank you very much for your time and effort. I really appreciate.

Now I safe to delete 0 qty records from stock table. For that, do  I need to use separate delete query, right? Sir, may I know is there   a way to do it in single query,  I mean like a store procedure.

[Barand]

If it isn't a process that is being called from multiple scripts then I don't see the point of putting it in a stored procedure. Just run your delete query after the update.

[thara]

Thank you very much! You perfectly resolved my issue, I am once again very grateful to you.

Meanwhile, before I got your query, I also made some effort for this. Of course I thought this could not be done without a store procedure. But you did it. Sir, you are a mysql geneious 😄

This is my attempt. But I coudn't get it to work.
 

CREATE PROCEDURE `UpdateStock` (
  IN @item_id INT,
  IN @sales_qty INT
)
BEGIN
 
  -- Fetch total stock in hand
  DECLARE stock_tot INT
  SET stock_tot = (SELECT SUM(qty) FROM stock WHERE item_id = @item_id)
 
  -- Check if the available stock is less than sales quantity
  IF stock_tot < @sales_qty
  BEGIN
    SELECT 'Stock not available' AS message
  END
 
  DECLARE @stock_id INT
  DECLARE @quantiti_in_hand INT
  -- Declare a CURSOR
  DECLARE @get_stock_id CURSOR
 
  SET @get_stock_id = CURSOR FOR
  SELECT stock_id, qty
    FROM stock
    WHERE item_id = @item_id AND qty_type = 'a'
    ORDER BY stock_id
 
  -- Open the CURSOR
  OPEN @get_stock_id

  -- Fetch record from the CURSOR
  FETCH NEXT
  FROM @get_stock_id INTO @stock_id, @quantiti_in_hand

  -- Loop if record found in CURSOR
  WHILE @@FETCH_STATUS = 0
  BEGIN
    -- Check if sales quantity becomes 0
    IF @sales_qty = 0
    BEGIN
      PRINT 'Updated Successfully'
      RETURN 1
    END
    -- If sales Qty is less than or equal to Quantity In Hand
    IF @sales_qty <= @quantiti_in_hand
    BEGIN
      UPDATE stock
      SET qty = qty - @sales_qty
      WHERE stock_id = @stock_id
      
      SET @sales_qty = 0
    END
    -- If sales Qty is greater than Quantity In Hand
    ELSE
    BEGIN
      UPDATE stock
      SET qty = 0
      WHERE stock_id = @stock_id

      SET @sales_qty = @sales_qty - @quantiti_in_hand
    END
    
    FETCH NEXT
    FROM @get_stock_id INTO @stock_id, @quantiti_in_hand
  END
    
  -- Close and  Deallocate CURSOR
  CLOSE @get_stock_id
  DEALLOCATE @get_stock_id
 
END