1

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?

2
  • Is there any logic to the new ordering you're choosing?...(e.g. based on another column such as a CreatedDate, or LastName, etc) or it's random?
    – J.D.
    May 26 at 11:15
  • The only logic is to exchange the positions of the rows and preserve id column, this is just a random example that came out of my head :) May 31 at 9:55
0

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

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

2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.