CASE...WHEN...THEN..., bug or not?

[Jeremy March]

Using CASE... WHEN... THEN in my update query doesn't behave the way I 
expect it to.  It's using the result of the query in the second WHEN 
condition rather than the original value.  See this example:

CREATE TABLE t1 (col_a tinyint not null, col_b tinyint not null);

INSERT INTO t1 VALUES (1,1);

UPDATE t1 SET col_a = CASE WHEN col_a = 1
                          THEN col_a + 1
                          ELSE 0
                     END,
             col_b = CASE WHEN col_a = 1
                          THEN col_b + 1
                          ELSE 0
                     END;
mysql> select * from t1;
+-------+-------+
| col_a | col_b |
+-------+-------+
|     2 |     0 |
+-------+-------+
1 row in set (0.00 sec)
Shouldn't this return 2 in both columns?

If this is the way CASE...WHEN...THEN... is supposed to behave, how can I 
make a query update two fields to one of two (or more) values depending on 
the value of one column without the result of one affecting the other.  I 
can't use two update queries for the same reason.

Thanks very much,
Jeremy
_________________________________________________________________
online games and music with a high-speed Internet connection!  Prices start 
at less than $1 a day average.  https://broadband.msn.com (Prices may vary 
by service area.)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]