Re: Using multiple 'where' statements in an update

[Jay Alverson]

My bad I forgot about the NULL if no ELSE is specified.

Try this it works for me...

mysql> 
mysql> # url:  http://forums.mysql.com/read.php?52,276357
mysql> 
mysql> select version();
+---------------------+
| version()           |
+---------------------+
| 5.0.67-community-nt | 
+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> use test;
Database changed
mysql> 
mysql> drop table if exists settings;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create table settings (name varchar(100), da_value integer);
Query OK, 0 rows affected (0.05 sec)

mysql> 
mysql> #---------------------------------------------
mysql> #  3 of each type...
mysql> #---------------------------------------------
mysql> 
mysql> insert into settings values
    -> ("jail_count", 0),
    -> ("hospital_count", 0),
    -> ("solitary_count", 0),
    -> ("jail_count", 0),
    -> ("hospital_count", 0),
    -> ("solitary_count", 0),
    -> ("jail_count", 0),
    -> ("other_count", 77),      # <-- just for testing how it works with other data...
    -> ("hospital_count", 0),
    -> ("solitary_count", 0);
Query OK, 10 rows affected (0.01 sec)
Records: 10  Duplicates: 0  Warnings: 0

mysql> 
mysql> select * from settings;
+----------------+----------+
| name           | da_value |
+----------------+----------+
| jail_count     |        0 | 
| hospital_count |        0 | 
| solitary_count |        0 | 
| jail_count     |        0 | 
| hospital_count |        0 | 
| solitary_count |        0 | 
| jail_count     |        0 | 
| other_count    |       77 | 
| hospital_count |        0 | 
| solitary_count |        0 | 
+----------------+----------+
10 rows in set (0.00 sec)

mysql> 
mysql> #  set the variables since we're not using PHP...
mysql> 
mysql> set @jc := 10, @hc :=20, @sc := 30;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> #---------------------------------------------
mysql> #  run the update...
mysql> #---------------------------------------------
mysql> 
mysql> update settings
    -> 
    -> set
    ->  da_value = case when name = 'jail_count'     then @jc else da_value end,
    ->  da_value = case when name = 'hospital_count' then @hc else da_value end,
    ->  da_value = case when name = 'solitary_count' then @sc else da_value end;
Query OK, 9 rows affected (0.00 sec)
Rows matched: 10  Changed: 9  Warnings: 0

mysql> 
mysql> select * from settings order by 1 asc;
+----------------+----------+
| name           | da_value |
+----------------+----------+
| hospital_count |       20 | 
| hospital_count |       20 | 
| hospital_count |       20 | 
| jail_count     |       10 | 
| jail_count     |       10 | 
| jail_count     |       10 | 
| other_count    |       77 | 
| solitary_count |       30 | 
| solitary_count |       30 | 
| solitary_count |       30 | 
+----------------+----------+
10 rows in set (0.01 sec)

mysql> 
mysql> notee

>

Thanks, Jay