mysql procedure loop with increment

Question

Please, know I'm learning MySQL as my application continues to grow. Please keep that in mind should you be kind enough to answer my question.

I'm trying to achieve the following

I added a new row to my table, keeping track of the game number in the round, thus it will have values 1,2,3,4.... when round ends it will be reset to 1 etc

I read in the MySQL manual about loops and came up with this, however it says my syntax is wrong, I would appreciate it if a more experienced user may look this over for me.

CREATE PROCEDURE inc()
    BEGIN
      DECLARE v1 INT 

      WHILE `round` ='1'

        SET v1 = v1 + 1;
    update events set `round_game_nr` ='v1'
      END WHILE;
    END;

I'm trying to achieve this

where 1st column is round and 2nd column is nr of games

you had missing ; after update, and WHILE .... DO

Yu Yenkan
– Yu Yenkan

2015-05-18 07:24:11 +00:00
Commented May 18, 2015 at 7:24 — Yu Yenkan
– Yu Yenkan, Commented May 18, 2015 at 7:24

Subhra Sekhar Mukhopadhyay · Accepted Answer · 2022-05-03 18:20:43Z

1

CREATE PROCEDURE inc()
BEGIN
  DECLARE v1 INT;    --semicolon missing

  WHILE `round` ='1' DO    --Do missing

    SET v1 = v1 + 1;
    UPDATE events SET `round_game_nr` =v1;   --semicolon missing, v1 should not with ``
  END WHILE;
END;

edited May 3, 2022 at 18:20

Subhra Sekhar Mukhopadhyay

15414 bronze badges

answered May 18, 2015 at 7:28

Yu Yenkan

7652 gold badges9 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tim C Over a year ago

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3 `

Tim C Over a year ago

hmmmm,im running this in phpmyadmin maybe that has some effect...ill investigate and let you knw, thanks mate

peter_the_oak Over a year ago

I also get this "error at line 3" with your online syntax checker. The usual way of commenting MySQL is -- , two dashes and blank, or #, or /* ... */.

Yu Yenkan Over a year ago

@TimothyCoetzee maybe because of your variable naming, I used those variable without ` ` just directly while round = '1' DO and so on

Yu Yenkan Over a year ago

@peter_the_oak lolz, I not sure how to commenting in MYSQL also, but you should know..... you can remove it =_=

Collectives™ on Stack Overflow

mysql procedure loop with increment

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related