Swap Consecutive Seat IDs in SQL

Question

Write a SQL query to swap the `id` of every two consecutive students in a `seat` table. If the number of students is odd, the id of the last student is not swapped.

Accepted Answer

Optimal Solution:
SELECT 
  CASE 
    WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM seat) THEN id
    WHEN id % 2 = 1 THEN id + 1
    ELSE id - 1
  END AS id,
  student
FROM seat
ORDER BY id ASC;

Technical Explanation:
Odd IDs become `id + 1`, even IDs become `id - 1`. If an odd ID is the last ID (`id = MAX(id)`), it remains untouched.

id	student
1	Abbot
2	Doris
3	Emerson
4	Green
5	Jeames

id	student
1	Doris
2	Abbot
3	Green
4	Emerson
5	Jeames