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>
[download]

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
[download]

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.
[download]

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.

am i missing something?

x=5
y=6

x=x+y # x = 5+ 6 = 11, yes?
y=x-y # y = 11 - 6 = 5, yes?
[download]

But of course only with numerical values, not with string data.

CountZero

"If you have four groups working on a compiler, you'll get a 4-pass compiler." - Conway's Law

Thanks alot. You saved me now half a year later