https://www.youtube.com/watch?v=Sq1kM3jVU68&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=28

-- Create the seats table
CREATE TABLE seats (
    id INT,
    student VARCHAR(10)
);

-- Insert data into the seats table
INSERT INTO seats (id, student) VALUES 
(1, 'Amit'),
(2, 'Deepa'),
(3, 'Rohit'),
(4, 'Anjali'),
(5, 'Neha'),
(6, 'Sanjay'),
(7, 'Priya');

CASE WHEN

SELECT *,
CASE WHEN id = (SELECT max(id) FROM seats) and id%2=1 THEN id 
	WHEN id%2 = 0 THEN id-1
    ELSE id+1 END AS New_id
	FROM seats;

LAG()
LEAD()

SELECT *,
CASE WHEN id % 2 = 0 THEN LAG(id,1) OVER (ORDER BY id)
	ELSE LEAD(id,1,id) OVER (ORDER BY id) END AS New_id
FROM seats;

UPDATE

WITH new_seats AS (
SELECT *,
CASE WHEN id % 2 = 0 THEN LAG(id,1) OVER (ORDER BY id)
	ELSE LEAD(id,1,id) OVER (ORDER BY id) END AS New_id
FROM seats
)

UPDATE seats 
JOIN New_seats ON seats.id = new_seats.id
SET seats.id = New_seats.New_id
;