626. Exchange Seats

# Write your MySQL query statement below
# id, prev_student AS  student
# 1,2, 3,4, 5,6 -> 2,1, 4,3, 6,5
#

 SELECT
 seat1.id, COALESCE(seat2.student, seat1.student) AS student
 FROM 
    (
        seat seat1 
        LEFT JOIN 
        seat seat2 
        ON seat2.id = ((seat1.id + 1)^1)-1
    )
ORDER BY seat1.id;
  

# Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.

# The column id is continuous increment.
# Mary wants to change seats for the adjacent students.
# Can you write a SQL query to output the result for Mary?
# +---------+---------+
# |    id   | student |
# +---------+---------+
# |    1    | Abbot   |
# |    2    | Doris   |
# |    3    | Emerson |
# |    4    | Green   |
# |    5    | Jeames  |
# +---------+---------+
# For the sample input, the output is:
# +---------+---------+
# |    id   | student |
# +---------+---------+
# |    1    | Doris   |
# |    2    | Abbot   |
# |    3    | Green   |
# |    4    | Emerson |
# |    5    | Jeames  |
# +---------+---------+