SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Oct 2011
    Posts
    21
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Update one table where two tables are joined - HELP

    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
    Code MySQL:
    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

    Code MySQL:
    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

    I just realized I posted this in the PHP forum, I will re-post in mysql (i don't know how to move it)
    Last edited by chemicaluser; Aug 8, 2013 at 14:12. Reason: posted in wrong section

  2. #2
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,058
    Mentioned
    152 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by chemicaluser View Post
    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

    Code MySQL:
    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

    I just realized I posted this in the PHP forum, I will re-post in mysql (i don't know how to move it)
    Quite the contrary, that is the reason it isn't updating a single record. You really need to add && t1.data_id = @data_id so it knows which row to update. However, you also need to set @data_id or pass it into your stored procedure for this to work.

    I'd try something like this in your while loop:
    Code MySQL:
    		-- 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
    			ORDER BY t1.data_id
    			LIMIT @y, 1); -- so you grab the correct row for each iteration of the while loop
     
    		-- extract data_id and store into variable
    			SET @data_id = (SELECT t1.data_id 
    				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
    			ORDER BY t1.data_id
    			LIMIT @y, 1); -- so you grab the correct row for each iteration of the while loop
     
    		-- 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
    				&& t1.data_id = @data_id;
     
    		-- Increments the counter
    			SET y = y + 1;
    Be sure to congratulate Patche on earning July's Member of the Month
    Go ahead and blame me, I still won't lose any sleep over it
    My Blog | My Technical Notes


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •