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 )
There is no way to achieve that with exactly one query*.
However, you could use a SQL transaction (if you use InnoDB) - this way you have the guarantee that the data is consistent.
start transaction;
select * from table for update;
... do the updates here...
commit;
The for update keywords causes these rows to be locked (for other writing queries or for other for update queries) until the transaction ends (all other queries will see the state before the transation or the result after the whole transaction finished).
(*) You just need to update 4 rows (maximum). The two you want to exchange and two rows pointing to them as previous. BUT, in order to know which ones to update you need to do (one or two) select queries - and there you need a transaction to keep data consistent, because at least in MySQL, you cannot combine an update query with select subqueries on the same table.
You are probably better off doing multiple updates wrapped in a transaction. It's the cleanest code and it should be acceptably fast. If you insist though ...