MYSQL - Set Clause not updating variable value

Do excuse my limited MySQL knowledge/skills. I have a table named flexcube_data

flexcube_data

The table is below

+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
| item_no | CIF_NO | CIF_NAME | PRIMARY_KEY | Collateral_Type | Collateral_Code | Stamped_to_Cover |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
| 1 | 801125 | John Doe | YES | 06 | J1 | 0 |
| 2 | 801125 | John Doe | YES | 08 | J5 | 0 |
| 3 | 801125 | John Doe | YES | 26 | J25 | 0 |
| 4 | 801125 | John Doe | YES | 26 | J25 | 0 |
| 5 | 801125 | John Doe | YES | 89 | J53 | 0 |
| 6 | 801125 | John Doe | YES | 98 | J57 | 0 |
| 7 | 801125 | John Doe | YES | 58 | J88 | 0 |
| 8 | 800102 | John Doe | | 19 | J03 | 0 |
| 9 | 800102 | Mary Jane | YES | 22 | J1 | 0 |
| 10 | 800102 | Mary Jane | YES | 90 | J5 | 0 |
| 11 | 800102 | Mary Jane | YES | 01 | J25 | 0 |
| 12 | 800102 | Mary Jane | YES | 77 | J25 | 0 |
| 13 | 800102 | Mary Jane | YES | 42 | J53 | 0 |
| 14 | 800102 | Mary Jane | YES | 25 | J57 | 0 |
| 15 | 800102 | Mary Jane | YES | 36 | J88 | 0 |
| 16 | 800102 | Mary Jane | | 32 | J03 | 0 |
| 17 | 563021 | Jack Daniels | YES | 58 | ab22 | 0 |
| 18 | 563021 | Jack Daniels | YES | 51 | ca55 | 0 |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+


My Goal is to:

My Expected results for the tmp_table should be

+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
| item_no | CIF_NO | CIF_NAME | PRIMARY_KEY | Collateral_Type | Collateral_Code | Stamped_to_Cover |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+
| 1 | 801125 | John Doe | YES | 06 | J1 | 0 |
| 9 | 800102 | Mary Jane | | 19 | J03 | 0 |
| 17 | 563021 | Jack Daniels | YES | 58 | ab22 | 0 |
+---------+--------+--------------+-------------+-----------------+-----------------+------------------+


My sample codes are as follows:

DELIMITER //
DROP PROCEDURE if exists FLEX //
use members_db;
Drop table if exists tmp_table;
CREATE TABLE tmp_table (
item_no INT AUTO_INCREMENT,
CIF_NO VARCHAR(255),
CIF_NAME VARCHAR(255),
Collateral_Type_1 VARCHAR(255),
Collateral_Code_1 VARCHAR(255),
Stamped_to_Cover_1 BIGINT,
Collateral_Type_2 VARCHAR(255),
Collateral_Code_2 VARCHAR(255),
Stamped_to_Cover_2 BIGINT,
Collateral_Type_3 VARCHAR(255),
Collateral_Code_3 VARCHAR(255),
Stamped_to_Cover_3 BIGINT,
Collateral_Type_4 VARCHAR(255),
Collateral_Code_4 VARCHAR(255),
Stamped_to_Cover_4 BIGINT,
Collateral_Type_5 VARCHAR(255),
Collateral_Code_5 VARCHAR(255),
Stamped_to_Cover_5 BIGINT,
PRIMARY KEY (item_no)
);
Drop table if exists flexcube_table;
CREATE TABLE flexcube_table (
item_no INT AUTO_INCREMENT,
CIF_NO VARCHAR(255),
CIF_NAME VARCHAR(255),
PRIMARY_KEY VARCHAR(255),
Collateral_Type VARCHAR(255),
Collateral_Code VARCHAR(255),
Stamped_to_Cover BIGINT,
PRIMARY KEY (item_no)
);
/* I INSERTED A COUPLE OF VALUES IN THE FLEXCUBE TABLE AS A SAMPLE */
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","06","J1","$20,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","08","J5","$22,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","26","J25","$5,100");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","26","J25","$5,100");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","89","J53","$111,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","98","J57","$118,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("801125","YES","John Doe","58","J88","$103,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","","John Doe","19","J03","$114,000");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","22","J1","$20,125");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","90","J5","$88,135");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","01","J25","$3,401");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","77","J25","$8,301");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","42","J53","$25,501");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","25","J57","$82,101");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","YES","Mary Jane","36","J88","$156,222");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("800102","","Mary Jane","32","J03","$187,256");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("563021","YES","Jack Daniels","58","ab22","$10,110");
insert into flexcube_table (CIF_NO,PRIMARY_KEY,CIF_NAME,Collateral_Type,Collateral_Code,Stamped_to_Cover) values("563021","YES","Jack Daniels","51","ca55","$26,725");
CREATE PROCEDURE FLEX()
BEGIN
DECLARE total_flex_rows INT DEFAULT 0;
DECLARE tmp_table_rows INT DEFAULT 0;
DECLARE counter INT DEFAULT 0;
DECLARE tmp_table_counter INT DEFAULT 0;
DECLARE FLEXCUBE_CIF_NO VARCHAR(255);
DECLARE TEMPORARY_CIF_NO VARCHAR(255);
DECLARE MATCH_FOUND INT DEFAULT 0;
DECLARE COLLATERAL_EXIST INT DEFAULT 0;
/* GET TOTAL ROWS IN ORIGINAL TABLE TO LOOP THROUGH AND STORE IT INTO VARIABLE*/
SELECT COUNT(*) FROM flexcube_table INTO total_flex_rows;
SET counter=0;
/* GET TOTAL ROWS IN TMP TABLE AND STORE IT IN VARIABLE */
SET tmp_table_rows=(SELECT COUNT(*) FROM tmp_table);
IF tmp_table_rows=0 THEN
INSERT INTO tmp_table (CIF_NO) VALUES("1");
END IF;
/* LOOP THROUGH EVERY ROW IN ORIGINAL TABLE TO DETERMINE IF CIF_NO VALUE EXIST IN TEMPORARY TABLE */
WHILE counter<=total_flex_rows DO
/* SET MATCH FOUND TO 0 - NO MATCH IS FOUND AS YET */
SET MATCH_FOUND=0;
SET FLEXCUBE_CIF_NO="";
SET FLEXCUBE_CIF_NO=(SELECT CIF_NO FROM flexcube_table WHERE PRIMARY_KEY="YES" LIMIT counter,1);
/* FOR EACH CIF_NO VALUE IN ORIGINAL TABLE, LOOP THROUGH EVERY ROW IN TEMPORARY TABLE TO CHECK IF VALUE ALREADY EXISTS */
SELECT COUNT(*) FROM tmp_table INTO tmp_table_rows;
sub_loop:WHILE tmp_table_counter<=tmp_table_rows DO
/* THE PROBLEM IS TEMPORARY_CIF_NO IS NOT NEW VALUES */
SET TEMPORARY_CIF_NO=(SELECT CIF_NO FROM tmp_table LIMIT tmp_table_counter,1);
IF FLEXCUBE_CIF_NO = TEMPORARY_CIF_NO THEN
/* WE CIF_NO IN ORIGINAL TABLE IS EQUAL TO CIF_NO VALUE IN TEMPORARY TABLE MATCH_FOUND=1 (TRUE) */
SET MATCH_FOUND=1;
SELECT "I FOUND A MATCH!!!!!!!!!!!!";
SET COLLATERAL_EXIST=0;
/* LEAVE WHILE LOOP SINCE THERE IS NO NEED TO CONTINUE FINDING CIFS */
LEAVE sub_loop;
END IF;
/* CONTINUE TEMPORARY TABLE ROW COUNTER INCREMENTING COUNTER */
SET tmp_table_counter=tmp_table_counter+1;
END WHILE sub_loop;
/* IF NOT MATCH IS FOUND THEN ADD NEW CIF_VALUE TO TEMPORARY TABLE */
IF MATCH_FOUND=0 THEN
/*IF NO MATCH IS FOUND INSERT THE NEW CIF_NO VALUE IN THE TEMPORARY TABLE */
INSERT INTO tmp_table (CIF_NO) VALUES((SELECT (CIF_NO) FROM flexcube_table LIMIT counter,1));
END IF;
SET counter = counter + 1;
SET MATCH_FOUND=0;
END WHILE;
End//
DELIMITER //
CALL FLEX();
select * from tmp_table;


ISSUE

When Looping through rows in the Temporary table, I am storing the CIF_NO value of each row in a variable called TEMPORARY_CIF_NO .
The Line SELECT TEMPORARY_CIF_NO; is returning NULL despite I set TEMPORARY_CIF_NO VALUE to be the CIF_NO value of the current row in the temporary table.

TEMPORARY_CIF_NO

SELECT TEMPORARY_CIF_NO;

NULL

TEMPORARY_CIF_NO

QUESTION

Why is the variable TEMPORARY_CIF_NO returning NULL?
I prefer a solution that builds on my existing codes since the codes are just a sample from the original.

TEMPORARY_CIF_NO

NULL

select min(item_no), cif_no, max(cif_name), ... from table group by cif_no

...

set match_found = (select count(*) from tmp_table where cif_no = FLEXCUBE_CIF_NO)

LIMIT counter,1

order by unique_column_combo

1 Answer
1

First Issue

The Set Clause was working as expected.
The tmp_table_counter variable keep ticking from the last count after every full loop.

tmp_table_counter

That is, I was not reseting the tmp_table_counter variable to 0 after looping through all rows in the tmp_table.

tmp_table_counter

The Corrected Code is:

SET tmp_table_counter=0; /* I NEEDED THIS LINETO RESET THE COUNTER BEFORE LOOPING AGAIN
SET MATCH_FOUND=0;
sub_loop:WHILE tmp_table_counter<=tmp_table_rows DO
..................
LEAVE sub_loop;


Second Issue

line below

`SET FLEXCUBE_CIF_NO=(SELECT CIF_NO FROM flexcube_table WHERE PRIMARY_KEY="YES" LIMIT` counter,1);


Why am I looping through every row in the original (flexcube_data) table and not retrieving CIF_NOs without any conditions?

flexcube_data

CIF_NO

The Logical Solution was Create a Primary Key Variable and store the primary key of the current row
and then change

IF FLEXCUBE_CIF_NO = TEMPORARY_CIF_NO THEN


TO

IF FLEXCUBE_CIF_NO = TEMPORARY_CIF_NO AND PRIMARY_KEY="YES" THEN


Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.