"Invalid use of group function" when calling procedure

Question

MySQL newbie here! I have a Table "Seats" consisting of ids (primary key) and student names. My task is to write an SQL query to swap the seat id of every two consecutive students. If the number of students is odd, the id of the last student is not swapped. id is a continuous increment and the result table should be ordered by id in ascending order. See input and output below:

Input:

Seat table:
+----+---------+
| id | student |
+----+---------+
| 1  | Abbot   |
| 2  | Doris   |
| 3  | Emerson |
| 4  | Green   |
| 5  | Jeames  |
+----+---------+
Output: 
+----+---------+
| id | student |
+----+---------+
| 1  | Doris   |
| 2  | Abbot   |
| 3  | Green   |
| 4  | Emerson |
| 5  | Jeames  |
+----+---------+

This is my attempt:

CREATE TABLE Seats ( #skapar employee tabellen
  id INT PRIMARY KEY, 
  student VARCHAR(20)
);

INSERT INTO Seats VALUES(1, 'Abbot');
INSERT INTO Seats VALUES(2, 'Doris');
INSERT INTO Seats VALUES(3, 'Emerson');
INSERT INTO Seats VALUES(4, 'Green');
INSERT INTO Seats VALUES(5, 'Jeams');

Now I make a procedure:

DELIMITER //
CREATE PROCEDURE Test() 
  BEGIN
    DECLARE x INT;
    DECLARE nbrSeats INT;
    SET x = 1;
    SET nbrSeats = COUNT(Seats.id);
    WHILE x < nbrSeats DO
        UPDATE Seats
            SET Seats.id = 0
            WHERE Seats.id = x+1;
        UPDATE Seats
            SET Seats.id = x+1
            WHERE Seats.id = x;
        UPDATE Seats
            SET Seats.id = x
            WHERE Seats.id = 0;
        SET x = x+2;
    END WHILE;
  END //

And here comes the problem after calling :

DELIMITER ;
CALL Test();
SELECT *
FROM Seats
ORDER BY Seats.id ASC;

I am returned the error: SQL Error (1111): Invalid use of group function. I think it's safe to say that the problem stems from SET nbrSeats = COUNT(Seats.id); because doing SET nbrSeats = 5; works just fine for the example input above. I know there's probably a much better way to solve the problem but since I am still in the beginning of the learning phase I would still like to understand what goes wrong. Thanks!

Answer 1

To count the id use SELECT COUNT(id) from Seats.

Try:

DELIMITER //
CREATE PROCEDURE Test() 
  BEGIN
    DECLARE x INT;
    DECLARE nbrSeats INT;
    SET x = 1;
    SET nbrSeats = (SELECT COUNT(id) from Seats);
    WHILE x < nbrSeats DO
        UPDATE Seats
            SET Seats.id = 0
            WHERE Seats.id = x+1;
        UPDATE Seats
            SET Seats.id = x+1
            WHERE Seats.id = x;
        UPDATE Seats
            SET Seats.id = x
            WHERE Seats.id = 0;
        SET x = x+2;
    END WHILE;
  END //
DELIMITER ;