Hello.

There is the following procedure:

 BEGIN 
 Declare uid VARCHAR (300);
 Declare influence integer;
 Declare currentPlace integer default 0;
 / * hadler variable * /
 Declare done integer default 0;
 / * Cursor declaration * /
 Declare myCursor Cursor for SELECT `rating`.`id`,` rating_weekly`.`ininfluence` FROM `rating` INNER JOIN` rating_weekly` ON `rating`.`id` =` rating_weekly``id`; 
 / * HANDLER * /
 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done done = 1;
 / * open the cursor * /
 Open myCursor;
 / * extract data * /
 WHILE done = 0 DO 
   FETCH myCursor INTO uid, influence;
   SELECT count (*) FROM `rating_weekly` WHERE` influence`> = influence INTO currentPlace; 
   UPDATE `rating` SET` bestWeeklyTop` = currentPlace WHERE `id` = uid;
 END WHILE;
 / * close the cursor * /
 Close myCursor; 
 END

The bottom line is that the request

 SELECT count (*) FROM `rating_weekly` WHERE` influence`> = influence

constantly returns the same value and writes it to the currentPlace variable. The sample directly depends on the condition influence > = influence , where the influence variable is constantly updated, but for some reason this is ignored and the currentPlace variable always contains the total number of rows in the table. Making this request outside the procedure, the result is correct.

Also, I checked the values ​​of all variables inside the loop - they are updated.

Has anyone come across this? Maybe I have some obvious errors in the script?

ps write procedure for the first time

  • I can not imagine why this is happening. But I also cannot understand why there are any variables or cursors at all. After all, the same can be done in one query: UPDATE rating R INNER JOIN ( SELECT A.id, count(*) as currentPlace FROM rating_weekly A, rating_weekly B WHERE B.influence >= A.influence GROUP BY A.id ) C ON R.id=C.id SET R.bestWeeklyTop=C.currentPlace I can not 100% vouch for correctness, I do not have your base for testing. but I think that is very close to correct - Mike
  • @Mike Thanks for the reply! The fact is that I need to use the IF condition, in which I actually perform the update. To search for errors, the script simplified by cutting out the condition check. - user3742070
  • Checks for some conditions can also be put in one request :) - Mike
  • @Mike hmm, okay. I read in this direction. Thank you!) - user3742070

1 answer 1

I think you should rename the variable Declare influence integer; . Never name variables in the same way as table fields.

In your query SELECT count(*) FROM rating_weekly WHERE influence >= influence INTO currentPlace; Both influence is the name of the table column and you can’t tell the compiler that one of them is actually a variable! As a result, your query looks something like select count(*) ... where 5>=5 .

  • This is indeed the case with the name of the variable. Thank you very much! - user3742070