To swap values like that you are probably going to have to use a temp value in your SQL. In most languages that support XOR you can Swap Two ints Without Using a Third Variable Here's an old trick from C. If you have two ints a and b whose values you want to swap, the obvious way to
do so is with a third, temporary variable:
int c = a;
a = b;
b = c;
It can be done without a temporary variable,
however, using the bitwise xor operator ^:
a = a^b;
b = a^b;
a = a^b;
The operator ^ produces a new int, each of whose
bits is the result of xoring the two
corresponding bits from the operands (1 if
the bits are different, 0 otherwise). To see why the trick works, consider the singlebit case:
of a=1 and b=0.
a = a^b = 1 xor 0 = 1
b = a^b = 1 xor 0 = 1
a = a^b = 1 xor 1 = 0
Even though you initially overwrite the value of
a, no information is lost; its encoding
simply changes. In the case of larger (multibit)
numbers, each pair of bits will be swapped
in the same way, and so the entire int values are swapped.
However that is unlikely to be any use to you at all ;) as I don't see an *easy* way to implement this in SQL (you might XOR into a collision with an existing ID value) so I would probably just do it with a temp value (must not exist in the table already!) so you free up the id value.
mysql> select * from test;
+++
 id  num 
+++
 1  100 
 2  200 
+++
2 rows in set (0.00 sec)
mysql> update test set id=666 where id=1; update test set id=1 where i
+d=2; update test set id=2 where id=666;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from test;
+++
 id  num 
+++
 2  100 
 1  200 
+++
2 rows in set (0.00 sec)
mysql>
