non-perl SQL question

[Anonymous Monk]

Can you swap two values in SQL in one statement?

For instance, if I have records with an "order" value, and want to swap six and five around so that six becomes five and five becomes six, can I do it in one statement?

I'm not in a position to test this at the moment.

I was thinking you'd have to temporarily set the "order" value five to null, or a million or something, then make six into five and the temporarily-changed record into six.

Because if you do it like this:

set order = 5 where order = 6
set order = 6 where order = 5

then you're just going to end up with two sixes, aren't you? And if you do "limit =1" then you don't know which one you're going to get.

[Roger]

Just an idea, I haven't tested this yet...


update table set order_id=11-order_id where order_id in (5,6)

[tachyon]

Clever solution!

my $sql = sprintf "UPDATE table SET id = %d - id WHERE id IN ( %d, %d 
+)", 
             ($val1+$val2), $val1, $val2;

mysql> select * from test;
+------+------+
| id   | num  |
+------+------+
|    1 |  100 |
|    2 |  200 |
+------+------+
2 rows in set (0.00 sec)

mysql> UPDATE test SET id = 3 - id WHERE id IN (1,2);
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from test;
+------+------+
| id   | num  |
+------+------+
|    2 |  100 |
|    1 |  200 |
+------+------+
2 rows in set (0.00 sec)

mysql>

[CountZero]

Very clever solution (you see: it pays to have attended math-class!) but only if the field you try to swap is not a primary key field or indexed by "UNIQUE". Then it burps an error: #1062 - Duplicate entry '6' for key 1

[Roger]

Thanks. ;-)

I first had this idea 12 years ago, during a first year C programming lecture at University. The professor declared in the class that if you want to swap the values of two variables, you have to do it with a temporary variable:


X=5
Y=6

# to swap X and Y, introduce a temporary variable Z
Z=X
X=Y
Y=Z

I challenged his declaration and provided the following solution in 2 steps without a third variable:


X=X+Y
Y=X-Y
X=X-Y

# I know this solution is not perfect:
# when X and Y are big, there is a danger of overflow.

The lecturer was amazed and gave me an 'A' for the subject. *grin*

The solution set order_id=11-order_id where order_id in (5,6) is just another variant of the same trick.

Update: Thanks rkg to point out the missing bit. My memory got rusty. The original challenge was to swap the values without using a third temporary variable.

[tachyon]

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 xor-ing the two corresponding bits from the operands (1 if the bits are different, 0 otherwise). To see why the trick works, consider the single-bit 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 (multi-bit) 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>

[dbwiz]

Here is a pure SQL solution, which works only if the column being swapped is not a unique key.

mysql> select * from swaptable;
+----+------+
| id | num  |
+----+------+
|  5 |  100 |
|  6 |  200 |
|  7 |  300 |
+----+------+
3 rows in set (0.00 sec)

mysql> UPDATE swaptable SET id = CASE id WHEN 5 THEN 6 
    -> WHEN 6 THEN 5 END 
    -> WHERE id IN (5,6);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from swaptable;
+----+------+
| id | num  |
+----+------+
|  6 |  100 |
|  5 |  200 |
|  7 |  300 |
+----+------+
3 rows in set (0.00 sec)

mysql> UPDATE swaptable SET id = CASE id WHEN 5 THEN 6 
    -> WHEN 6 THEN 5 END 
    -> WHERE id IN (5,6);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> select * from swaptable;
+----+------+
| id | num  |
+----+------+
|  5 |  100 |
|  6 |  200 |
|  7 |  300 |
+----+------+
3 rows in set (0.00 sec)