Re: non-perl SQL question

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

[rkg]

am i missing something?

x=5
y=6

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

[CountZero]

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

[Anonymous Monk]

Thanks alot. You saved me now half a year later