vendredi 30 mars 2018

MySQL While Loop to fillup Table with incremental values where Auto-Increment is no option

My attempt to fill a MySQL Table with at the moment incremental values. Later on there could be changes why AUTO-INCREMENT is no solution here.

I tried to do a loop:

CREATE PROCEDURE fill()

BEGIN DECLARE v1 INT DEFAULT 1; DECLARE a INT DEFAULT 1; #autoIncrement DECLARE b INT DEFAULT 1; #counting upwards DECLARE c INT DEFAULT 1; #only 1 mixer DECLARE d INT DEFAULT 1; #product from 1-6

 WHILE v1 < 64 DO


    INSERT INTO `settingsoperationmixer`(`settingsOperationMixerId`,                    `settingsOperationId`, `mixerId`, `productId`) VALUES (a,b,c,d)

    IF d < 6 THEN SET d = d + 1;
    ELSE SET d = 1;

    SET b = b + 1;
    SET v1 = v1 + 1;
END WHILE;

END;

This should loop trough 63 times while the settingsOperationId will be 1-63 incremental and the productId should go from 1-6 and then start over again at 1

and get this error message:

Error

SQL query:

CREATE PROCEDURE fill()
BEGIN
DECLARE v1 INT DEFAULT 1

MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 3

Aucun commentaire:

Enregistrer un commentaire