MySQL (MariaDB) while loop

Question

Hey there,

I'm trying to update a table with rack and shelf locations in our storage room. there are 15 racks and each rack has 5 shelves. I'm running the loop to 20 to add some extra locations for when we get more shelves. So far this is the procedure I've been trying torun, but; I am getting a syntax error near my first END IF;

Here is my statement:

    drop PROCEDURE if exists updateLocations;
    DELIMITER //
    CREATE PROCEDURE updateLocations()
    begin
    DECLARE rack INT default 1;
    DECLARE shelf INT default 1;
    WHILE rack<21 DO
        insert into tblStorageLocations values ("", rack, shelf);
            IF (shelf=5, SET rack=rack+1, set rack=rack);
            END IF;
            IF (shelf<5, SET shelf=shelf+1, set shelf=1);
            END IF;
            END WHILE;
END;
    //
    DELIMITER ;


    ERROR 1064 (42000): 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 'IF;

    IF (shelf<5, SET shelf=shelf+1, set shelf=1);
    END IF;
    END WHILE' at line 8

Gordon Linoff · Accepted Answer · 2017-04-08 01:48:18Z

3

You are processing if as a function, rather than as a statement.

drop PROCEDURE if exists updateLocations;
DELIMITER //

CREATE PROCEDURE updateLocations()
BEGIN
    DECLARE rack INT default 1;
    DECLARE shelf INT default 1;
    WHILE rack < 21 DO
        INSERT INTO tblStorageLocations 
            VALUES ('', rack, shelf);
        IF shelf = 5 THEN
            SET rack = rack + 1;
        END IF;
        IF shelf < 5 THEN
            SET shelf = shelf + 1;
        ELSE 
            SET shelf = 1;
        END IF;
    END WHILE;
END;
//
DELIMITER ;

answered Apr 8, 2017 at 1:48

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

leech911 Over a year ago

Thank you very much, that was exactly what I was misunderstanding!

Collectives™ on Stack Overflow

MySQL (MariaDB) while loop

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related