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

    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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,215
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    could you please just take a moment and explain why there's a loop and what the heck all that code is doing
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •