mysql - MySQL Update Table Entries with While and If Statements Loop issue

Question

I have a table as followed with the attributes teamId and roundId and another table called teamRoundId to connect the two attribute. My goal is to replace the teamId/roundId connection with one attribute called teamRoundId

At the moment the attribute teamRoundId is filled up with some numbers because I was trying to get it right.

The Statement i think is closest to what I'm trying is:

drop procedure if exists j;
delimiter $$
CREATE PROCEDURE j()
BEGIN 
DECLARE v INT DEFAULT 1;
DECLARE c INT DEFAULT 0;
DECLARE d INT DEFAULT 1;

 WHILE v < 64 DO
    UPDATE `ssshortagerule`
    SET `teamRoundId` = v
    Where teamId = d AND roundId = c;
    IF c < 6 THEN
    SET c = c + 1;
    ELSE
    SET c = 0 AND d = d + 1;
    END IF;
    SET v = v + 1;
END WHILE;
END $$
delimiter ;
call j();

So teamId 1 and roundId 0 should be teamRoundID 1, teamId 1, roundId 1 -> teamRoundId 2 and so on until 63. What am I missing in my Statement?

To be said, that teamRoundId 59 to 63 was generated by that told Statement above. the 2 was manually added.

Additional information: There are 7 (0 to 6) Rounds and 9 Teams but not all of them have an entry in this Table

标签： mysqlif-statementwhile-loop

Answer 1

There can't be a AND Statement between 2 vars with the SET command:

drop procedure if exists j;
delimiter $$
CREATE PROCEDURE j()
BEGIN 
DECLARE v INT DEFAULT 1;
DECLARE c INT DEFAULT 0;
DECLARE d INT DEFAULT 1;

 WHILE v < 64 DO
    UPDATE `ssshortagerule`
    SET `teamRoundId` = v
    Where teamId = d AND roundId = c;
    IF c < 6 THEN
    SET c = c + 1;
    ELSE
    SET c = 0;
    SET d = d + 1;
    END IF;
    SET v = v + 1;
END WHILE;
END $$
delimiter ;
call j();