I have a file Test01 with a list of Invoice Numbers. The Invoice File (Invoices02) has many invoices where each invoice could have 5 or more rows.
The Test01 file starts with all rows for Field1 as blank. As each invoice number is read from Test01 then Field1 is marked as "X".
As each invoice number is read from the Test01 file, all rows in the Invoice file are marked as EXTRA1 = "X" for that Invoice Number.
I have developed a Stored Procedure as below to do this but it doesn't work. The error message is not meaningful. I would appreciate any guidance on this.
Mike
DELIMITER $$
CREATE PROCEDURE [IF NOT EXISTS] markInv02
BEGIN
DECLARE count1 INT DEFAULT 1;
WHILE count1 > 0
count1 = SELECT COUNT(invnbr) from Test01 where Field1 = " ";
if count1 > 0;
SELECT invnbr INTO @invValue FROM Test01 WHERE Field1 = " ";
update Test01 set Field1 = "X" where invnbr = @invValue;
UPDATE Invoices02 set EXTRA = "X" where invnbr = @invValue;
endif;
END WHILE;
END$$
DELIMITER ;
DELIMITER $$
CREATE PROCEDURE `markInv02`()
BEGIN
DECLARE count1 INT;
DECLARE invValue INT;
SET count1 = (SELECT COUNT(invnbr) FROM Test01 WHERE Field1 = " ");
WHILE count1 > 0 DO
SET invValue = (SELECT invnbr FROM Test01 WHERE Field1 = " ");
UPDATE Test01 SET Field1 = "X" WHERE invnbr = invValue;
UPDATE Invoices02 SET EXTRA = "X" WHERE invnbr = invValue;
SET count1 = (SELECT COUNT(invnbr) FROM Test01 WHERE Field1 = " ");
END WHILE;
END$$
DELIMITER ;
