Update one table where two tables are joined

Hi … hoping someone can help me out. I am having a problem figuring out how to write a query to update only 1 table (where 2 tables are joined). Using LIMIT does not work (which is noted in the manual) but i know there has to be a way around this hpwever I have had no luck and am hoping someone can at least point me in the right direction

Here is what my data looks like

table = data_table_base
data_id,Number
1,12345678911
2,76543211548
3,77668810124

Here is what my code looks like


DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `test`()
BEGIN

	DECLARE y INT;
		
		-- Set counter to 0
		SET y = 0;
		
		-- Get the total number of records in table which need to be updated
			SET @x = (SELECT COUNT(*) FROM rec_main.data_table_flag t1
						inner join rec_main.data_table_base t2 ON t1.data_id = t2.data_id
						WHERE t1.revCusNum IS NULL
							&& t2.PType='ng'
							&& t2.RecStat=1
							&& t2.CDR=1);
		

		-- Loop until counter reaches the total number of records
			WHILE y < @x DO	
		
		-- extract cusnum and store into variable
			SET @cusnum = (SELECT SUBSTRING(t1.Number FROM 1 FOR 7)
				FROM rec_main.data_table_base t1
				inner join rec_main.data_table_flag t2 ON t1.data_id = t2.data_id
			WHERE t2.revCusNum IS NULL
				&& t1.PType='ng'
				&& t1.RecStat=1
				&& t1.CDR=1
			LIMIT 1);
	
		-- update table
			UPDATE rec_main.data_table_base t2, rec_main.data_table_flag t1
				SET t1.revCusNum=@cusnum
			WHERE (t1.revCusNum IS NULL
				&& t2.PType='ng'
				&& t2.RecStat=1
				&& t2.CDR=1)
				&& t2.data_id = t1.data_id;

		-- Increments the counter
			SET y = y + 1;			
		END WHILE;

END
DELIMITER ;

When I run my code, this is the results i get in the table called data_table_flag

table = data_table_flag
data_id,revCusNum
1,1234567
2,1234567
3,1234567

However, I want to get this when I run it

table = data_table_flag
data_id,revCusNum
1,1234567
2,7654321
3,7766881

I know my problem has to do with this code here - but I would think that t2.data_id = t1.data_id would make it work


UPDATE rec_main.data_table_base t2, rec_main.data_table_flag t1
				SET t1.revCusNum=@cusnum
			WHERE (t1.revCusNum IS NULL
				&& t2.Type='ng'
				&& t2.RecStat=1
				&& t2.CDR=1)
				&& t2.data_id = t1.data_id;

Any suggestions? Thanks in advance :slight_smile:

could you please just take a moment and explain why there’s a loop and what the heck all that code is doing