Update cumulative stock balance in stock diary

This sql script can be used to keep cumulative stock balance in stockdiary table.

Execute below script once in mySQL.

ALTER TABLE STOCKDIARY ADD COLUMN STOCKBALANCE INT;
DROP PROCEDURE if exists updateStockBalance;
DELIMITER $$
CREATE PROCEDURE updateStockBalance()
BEGIN
 DECLARE done INT DEFAULT FALSE;
    DECLARE o_id VARCHAR(255);
    DECLARE balance INT;
    DECLARE cur1 CURSOR FOR SELECT ID FROM PRODUCTS;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;
    read_loop: LOOP
    FETCH cur1 INTO o_id;
    SET @balance = 0;
    SET @pid = o_id;
    UPDATE STOCKDIARY  SET STOCKBALANCE=@balance:=@balance + UNITS WHERE PRODUCT LIKE @pid AND STOCKBALANCE IS NULL ORDER BY DATENEW;
    IF done THEN
      LEAVE read_loop;
    END IF;
    END LOOP;
    CLOSE cur1;
    END$$
DELIMITER ;

Schedule below script to keep the stockbalance column updated:
CALL updateStockBalance();

Save