How can we swap values in MySQL?

Question

Suppose I have a table with the field 'sequence' , there are 2 records with sequence 2 and 3. I wanna swap these, ie the record with sequence 2 should get updated as 3 and vice versa. Is it possible to do it in a single query?

Btw.. there will be more than one record with sequence 2 and 3 ..I wanna update that as well

Answer 1

No you would be trying to mix a select query and an update query into one. This doesn't (to my knowlege) work with MySql.

Your best and safest bet is to do your select query and use PHP to swap the values and store the updated records with an update query.

Answer 2

No don't think it is possible in a single query, and I don't think it's even possible in 2. It is possible in 3.

The problem lies as follows: when u update the 2's into 3's (or the other way around) all ur seq 2 AND 3 are gonna be the same number for a fraction of a second (even in a single query, it is most likely still being executed in sequence). Because of that, all ur 2's and 3's are going to be converted to the same number.

To avoid this, do a standard swap
temp = a
a = b
b = temp

as far as SQL goes, do an UPDATE instead of an assign (=).
so it would look as follows
- update seq 2's to -1 (or any other unused sequence #)
- update seq 3's into seq 2's
- update seq -1 to seq 3's

hope this helps

P.S. selecting into PHP and swapping it there will probably take more time because u're doing 2 select and tons of updates (one per record), just think about it.

Answer 3

Yes.

Answer 4

the answer to your question is yes you can achieve this using nested sql