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

[thara]

Hello Sir,

I have found another case. Check below results

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        | 10.00 |       3 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | NULL         |
|        2 | a        | 10.00 |       2 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | NULL         |
|        3 | a        | 10.00 |       1 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | NULL         |
|        7 | a        |  5.00 |       1 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | NULL         |
|        8 | a        |  5.00 |       2 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | NULL         |
|        9 | a        |  5.00 |       3 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | NULL         |
|       13 | a        |  4.00 |       1 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | NULL         |
|       14 | a        |  4.00 |       2 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | NULL         |
|       15 | a        |  4.00 |       3 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | NULL         |
+----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+

First run sales

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

select * from stock;
+----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty  | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 7.00 |       3 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:04 |
|        2 | a        | 8.00 |       2 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:04 |
|        3 | a        | 9.00 |       1 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:04 |
|        7 | a        | 5.00 |       1 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:04 |
|        8 | a        | 3.00 |       2 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:04 |
|        9 | a        | 2.00 |       3 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:04 |
|       13 | a        | 3.00 |       1 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:04 |
|       14 | a        | 2.00 |       2 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:04 |
|       15 | a        | 1.00 |       3 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:04 |
+----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Second run sales

select * from sales;
+----------+---------+----------+-----------+
| sales_id | item_id | quantity | basket_id |
+----------+---------+----------+-----------+
|        1 |       1 |     1.00 |        99 |
|        2 |       2 |     2.00 |        99 |
|        3 |       3 |     3.00 |        99 |
|        4 |       1 |     1.00 |       100 |
|        5 |       2 |     2.00 |       100 |
|        6 |       3 |     3.00 |       100 |
+----------+---------+----------+-----------+

select * from stock;
+----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+
| stock_id | qty_type | qty  | item_id | stock_location_id | stock_type_id | purchase_id | created_date        | updated_date        |
+----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+
|        1 | a        | 4.00 |       3 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:52 |
|        2 | a        | 6.00 |       2 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:52 |
|        3 | a        | 8.00 |       1 |                 1 |             5 |           0 | 2022-06-08 16:17:05 | 2022-06-08 16:19:52 |
|        7 | a        | 5.00 |       1 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:52 |
|        8 | a        | 3.00 |       2 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:52 |
|        9 | a        | 2.00 |       3 |                 1 |             1 |          72 | 2022-06-08 16:18:03 | 2022-06-08 16:19:52 |
|       13 | a        | 3.00 |       1 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:52 |
|       14 | a        | 2.00 |       2 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:52 |
|       15 | a        | 1.00 |       3 |                 1 |             1 |          74 | 2022-06-08 16:18:32 | 2022-06-08 16:19:52 |
+----------+----------+------+---------+-------------------+---------------+-------------+---------------------+---------------------+

Sir, you can see how qty updates when it first runs. It updates all records belonging to item_id. But in second runs update works correctly.

What would be the issue?