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