Create Stored Procedure | 1172 Result consisted of more that one row

June 10, 2022

I would like to increase 3 numbers by the same percentage.
The problem is that these make an array and I get this error because of that "1172 Result consisted of more that one row"

SELECT ondergrens FROM salarisschaal INTO onder;
SELECT bovengrens FROM salarisschaal INTO boven;

I would like to increase them all what would be the best solution for this?

DELIMITER $$
CREATE PROCEDURE AlgemeneSalarisVerhoging(verhoging float)
BEGIN
    DECLARE onder float;
    DECLARE boven float;
    START TRANSACTION;
        SELECT ondergrens FROM salarisschaal INTO onder;
        SELECT bovengrens FROM salarisschaal INTO boven;
        UPDATE werknemer
            SET salaris = salaris / 100 * (100 + verhoging);
        UPDATE salarisschaal
            SET onder = onder / 100 * (100 + verhoging);
            SET boven = boven / 100 * (100 + verhoging);
    COMMIT;
END$$
DELIMITER ;

>Solution :

The problem was that you can only use 1 SET per UPDATE that way you don’t need to declare an array and it will execute on each row.

DELIMITER $$
CREATE PROCEDURE AlgemeneSalarisVerhoging(verhoging float)
BEGIN
    START TRANSACTION;
        UPDATE werknemer
            SET salaris = salaris / 100 * (100 + verhoging);
        UPDATE salarisschaal
            SET ondergrens = ondergrens / 100 * (100 + verhoging);
        UPDATE salarisschaal
            SET bovengrens = bovengrens / 100 * (100 + verhoging);
    COMMIT;
END$$
DELIMITER ;