I have to do a list in php that can be rearranged by the user. This is what I came up with:
Table layout:
ID | Name | previous
1 | "first" | NULL
2 | "second" | 1
3 | "third" | 2
4 | "fourth" | 3
Lets say I want to swap "third" with "second". The table would look like this after the query:
ID | Name | previous
1 | "first" | NULL
3 | "third" | 1
2 | "second" | 3
4 | "fourth" | 2
So what happens?
1) "Fourth" gets "second"'s value (from 3 to 2).
2) "Third" gets "second"'s previous value (from 2 to 1).
3) "Second" get's "fourth"'s previous value (from 1 to 3... obviously there is a conflict with 1) if it's in that order).
What I need is a query that does what I just explained. I don't want to use multiple queries, so that's why I want to use subqueries instead.
I already have a SELECT statement that gives me all the data of the 3 rows to be changed... But how do I update the columns of each row?:
SELECT * FROM test a
JOIN test b ON a.id = 3 AND b.id = ( SELECT id FROM test WHERE id < 3 ORDER BY id DESC limit 1 )
JOIN test c ON a.id = 3 AND c.id = ( SELECT id FROM test WHERE id > 3 ORDER BY id ASC limit 1 )