CODEWITHSUNDEEP
mysql
zerodeng
zerodeng

Reputation: 3

Can MYSQL CASE expression running well with the same WHEN condition?

as you see:

UPDATE activity
SET number = CASE
WHEN aid = 45 THEN
    number + 1 
WHEN aid = 43 THEN
    number + 1 
END
WHERE
    aid IN (45, 43)

this queries is running well. but:

UPDATE activity
SET number = CASE
WHEN aid = 45 THEN
    number + 1 
WHEN aid = 43 THEN
    number + 1 
WHEN aid = 45 THEN
    number + 3
WHEN aid = 49 THEN
    number + 1
END
WHERE
    aid IN (45, 43,49)

there is something wrong with the query.the second "WHEN aid=45..." is not work.The result is when aid=45 the number increase 1,but i want the number increase 4.

thanks for the answer.but i think my questions are described so simple.So,may be i should describe it in detailed. There are my PHP code which to update the different activity's number.The aid is primary key.

/*
$aids is an array which the key is the activity's primary key and the 
value is the activity's number.
example $aids = array('45'=>4,'43'=>1...)
In the array,i combine all the same aid to one element.so one aid can 
not be used twice by the case expression.And  all the number are total
number.
*/
$sql = 'update huodong_activity set applynumber = case ';
$aids_str = implode(',',array_keys($aids)); 
foreach($aids as $k=>$v){
      $sql .= sprintf(' when aid = %d then applynumber-%d',$k,$v);
}
$sql .= ' END WHERE aid IN ('.$aids_str.') ';
$db->query($sql);

my question is :

1.I really don't know why case expression doesn't work by "aid=45" used twice or more ?

2.I don't know how many rows could be updated by the "update case expression" or any limits ?

Upvotes: 0

Views: 205

Answers (1)

jpw
jpw

Reputation: 44891

The second version won't work as the case expression will match the first 45 and then stop. Just remove the WHEN aid = 45 THEN number + 1 and change the second to WHEN aid = 45 THEN number + 4 if you want to increase it by 4 like this:

UPDATE activity
SET number = CASE
WHEN aid IN (43, 49) THEN
    number + 1 
WHEN aid = 45 THEN
    number + 4
END
WHERE
    aid IN (45, 43, 49)

Upvotes: 1

Related Questions