Change column values but keep order in MySQL

Question

Lets consider I have a table:

CREATE TABLE student (
id int,
 name varchar(128)
);

INSERT INTO student (id,name) VALUES
(1,'John'),
(2,'Bob'),
(3,'Alice'),
(4,'Don'),
(5,'Eve');

I need the 'names' column to exchange places with next one: 1 with 2, 3 with 4 and etc, but keep the order of 'id'. The selected output should be something like this:

id  names
1 | Bob
2 | John
3 | Don
4 | Alice
5 | Eve

Is there an effective way to write query that would give me expected result?

Answer 1

You can self join student and use an alghorithm that fits toi find the corresponding ids

CREATE TABLE student (
id int,
 name varchar(128)
);

INSERT INTO student (id,name) VALUES
(1,'John'),
(2,'Bob'),
(3,'Alice'),
(4,'Don'),
(5,'Eve');

UPDATE student s1
JOIN student s2 ON s1.id = IF(s1.id % 2 = 0,s2.id + 1,s2.id - 1)
SET s1.name = s2.name

SELECT * FROM student

id | name 
-: | :----
 1 | Bob  
 2 | John 
 3 | Don  
 4 | Alice
 5 | Eve  

db<>fiddle here

Answer 2

You can do this without self-joining, and without executing the same command multiple times.

You just need LEAD and LAG window functions, and a rownumber:

UPDATE s
SET name = CASE WHEN rn % 2 = 0 THEN prevName ELSE nextName END
                  -- every row is either (rn modulo 2) = 1 or 0
FROM (
    SELECT *,
        prevName = LAG(name) OVER (ORDER BY id),
        nextName = LEAD(name, 1, name) OVER (ORDER BY id),
             -- the extra parameters mean you get the current value if there is no next row
        rn = ROW_NUMBER() OVER (ORDER BY id)
    FROM student
) s

db-fiddle

Answer 3

This sounds like an activity that a college teacher might come up with to see if someone understands math and has a little bit of imagination, as this sort of thing would almost never be required outside of an educational setting ... and I say this as someone who works in academia.

That said, one way this could be accomplished would be with a subquery that first determines the "proper" id. An UPDATE statement can then be used to match on the "proper_id" to fix the data. Here is the sub-query:

SELECT CASE WHEN id % 2 = 0 THEN id - 1 ELSE id + 1 END as proper_id,
       name
  FROM Student
 ORDER BY id

I'll leave the rest to you