0

I have 2 tables api(columns - api , api_context, api_id -->primary key) and api_request_summary(columns - api_context). i need to insert the number of times the api_context relevant to a particular api_id is repeated in the table curhittest1. both tables have multiple rows so i have used cursors to loop through the tables. i can correctly insert the values to the table but i need to check wether that api_id already exists in the curhittest1 table and if yes update and if no insert.

DELIMITER //
CREATE PROCEDURE curhit12()

BEGIN

 DECLARE done INT DEFAULT FALSE;
 DECLARE apiId, noOfHits int(11);
 Declare apiContext varchar(255);

 DECLARE cur1 CURSOR FOR Select api_id from api;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     

  OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO apiId;

    IF done THEN
      LEAVE read_loop;
    END IF; 

    select api_context into apiContext from api where api_id =apiId;  

    SELECT COUNT(*) FROM api_request_summary WHERE api_context=apiContext into noOfHits;

 IF exists (SELECT * FROM curhittest1 WHERE apiid = apiId) THEN

   UPDATE curhittest1
                      SET noofhits=noOfHits                        
                      WHERE apiid = apiId;
 ELSE

  insert into curhittest1(apiid,noofhits) values (apiId,noOfHits);
 END IF;

  END LOOP;

  CLOSE cur1;

END//
DELIMITER

When i use the following code only 1 row is added to the curhittest1 table. When the exact same code is used in sql procedures without cursors it displays so i'm assuming i need to do something differently when using with cursors. How can i add all the values to the table accurately?

  • Seems like one statement will do the whole task: INSERT ... ON DUPLICATE KEY UPDATE ... – Rick James Oct 28 '18 at 1:08
0

It worked after using INSERT ... ON DUPLICATE KEY UPDATE. Thank you @RickJames. For anyone interested here is the code

DELIMITER //
CREATE PROCEDURE curhit12()

BEGIN

 DECLARE done INT DEFAULT FALSE;
 DECLARE apiId, noOfHits int(11);
 Declare apiContext varchar(255);    
 DECLARE cur1 CURSOR FOR Select api_id from api;    
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     

  OPEN cur1;

read_loop: LOOP
    FETCH cur1 INTO apiId;

    IF done THEN
      LEAVE read_loop;
    END IF; 

    select api_context into apiContext from api where api_id =apiId;  

SET noOfHits = (SELECT COUNT(*) FROM api_request_summary 
                WHERE api_context=apiContext);

INSERT INTO api_hits (api_id,no_of_hits) VALUES (apiId,noOfHits)
   ON DUPLICATE KEY UPDATE no_of_hits=noOfHits;

  END LOOP;

CLOSE cur1;

END//
DELIMITER

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.