thara

In MySQL, I want to create a trigger with AFTER UPDATE triggering event for my "user" table. In next, I have a table named "user_log" which is use to store the modifications that occurred on the parent table "user" after any update commands. So, data in "user_log" table need be as follows: select * from user_log; +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | log_id | user_id | action | old_data | new_data | changed_date | read | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | 1 | 10 | Changed yyy's name | yyy | xxx | 2022-06-20 14:06:56 | no | | 2 | 10 | Changed xxx's address | No.111, | No.112, | 2022-06-20 19:07:38 | no | | 3 | 10 | Changed xxx's city | Old City Name | New City Name | 2022-06-20 19:07:38 | no | | 4 | 10 | Changed xxx's phone number | 011-5000000 | 011-4000000 | 2022-06-20 19:07:38 | no | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ As you can see from the data in the table above, it will update several columns at once. So I created my triger as follows, and its working for me. DELIMITER $$ DROP TRIGGER IF EXISTS `user_log` ; $$ CREATE TRIGGER `user_log` AFTER UPDATE ON `user` FOR EACH ROW BEGIN IF OLD.name <> NEW.name THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name ELSE '' END ); END IF; IF OLD.address <> NEW.address THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN OLD.address ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN NEW.address ELSE '' END ); END IF; IF OLD.city <> NEW.city THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN OLD.city ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN NEW.city ELSE '' END ); END IF; IF OLD.phone <> NEW.phone THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); END IF; END$$ DELIMITER ; My problem is, I have a lot more columns in the user table. Like I said, all columns or several of them are updated at once. In that case I have to add a large amount of INSERT query to my trigger. So here I would like to know if there is another suitable way to do this. I also tried it in this way. But its working only for one column. DROP TRIGGER IF EXISTS `user_log`; CREATE TRIGGER IF NOT EXISTS `user_log` AFTER UPDATE ON user FOR EACH ROW INSERT INTO user_log (user_id,action,old_data,new_data) VALUES ( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name WHEN (NEW.address <> OLD.address) THEN OLD.address WHEN (NEW.city <> OLD.city) THEN OLD.city WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name WHEN (NEW.address <> OLD.address) THEN NEW.address WHEN (NEW.city <> OLD.city) THEN NEW.city WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); Thank you.

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?

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

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.

Yes sir, its my mistake. now it's working nicely. One thing, the updated_date updates all records related to item_id.

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

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 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

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

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 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+--------------+

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?

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 | +----------+----------+-------+---------+-------------------+---------------+-------------+---------------------+---------------------+

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.

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.

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)

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.