1

i'm doing my first DB on mySQL and I want to make an underground like system. To start the system I want to make them go from one station to another on a fixed time of 2 seconds so I use the following code

delimiter |
CREATE EVENT runBlue
ON SCHEDULE EVERY 2 SECOND
 DO
    BEGIN
     WHILE (SELECT Actual_Station FROM route WHERE Actual_Station) < 311 DO
     UPDATE mydb.route SET Actual_Station = (Actual_Station+1);
     END WHILE;
     WHILE (SELECT Actual_Station FROM route WHERE Actual_Station) >= 300 DO 
     UPDATE mydb.route SET Actual_Station = (Actual_Station-1);
     END WHILE;
    END |
    

delimiter ;

What concerns me is that there is no increment to the Actual_Station that is represented by an INT, the reason why I am doing this is because the underground is divided in 3 routes, this is the example for the first one that goes form station 300 to station 311.

Hugo Teles
  • 11
  • 1
  • Then it's much easier and faster to identify the groups if the routes had a group_id column instead of magic numbers. However, I'm not sure what you're trying to accomplish. It seems like you're incrementing and decrementing all the stations. Unless `route` and `mydb.route` are different? Could you show your schema and explain what you're trying to do in more detail? – Schwern Dec 01 '20 at 20:34
  • An RDBMS is for the storage and retrieval of relational data. I don't understand what this has to do with that. – Strawberry Dec 01 '20 at 20:41
  • I want to go from station 300 to station 311 and back this explains the increment and the decrement, these magic numbers are the stations id. There is no increment nor decrement happening here, I think there is a problem in the while condition – Hugo Teles Dec 01 '20 at 21:03
  • You are first incrementing the station until it has the value of 311, then the first while loop stops, and the second while loop will decrement the value to 299... There does not seem to be an error in a while condition, but an error in the understanding how `WHILE.. DO ...END WHILE` works. (maybe read [docs](https://dev.mysql.com/doc/refman/8.0/en/while.html) again?) – Luuk Dec 02 '20 at 06:59
  • I'm testing it, there is no increment nor decrement – Hugo Teles Dec 02 '20 at 20:50

0 Answers0