具有多个值的MySQL Update查询

I have a table in the database with records like following:

我在数据库中有一个表，其中包含以下记录：

match_id | guess | result
   125   |   1   |   0
   130   |   5   |   0
   233   |   11  |   0
   125   |   2   |   0

my users choose a guess for each match and I have a function that calculate the result of the guess depending on the result of the match: if the guess is right the result will be (1) if it is wrong the result will be (2) if the match did not finish yet the result will be (0 default) I have eleven possibilities for guesses (more than one could be right at the same time) for example : if I have a match whit id=125 and I have all the guesses wrong except 8,11 so i should update the result field for all matches that have the match id and their guess is 8 or 11 (i will give 1 for this result field) and I want to give (2) for the other guesses of the same match

我的用户为每个匹配选择一个猜测，我有一个根据匹配结果计算猜测结果的函数：如果猜测正确，结果将是（1）如果错误结果将是（2））如果匹配没有完成，结果将是（0默认值）我有十一种可能的猜测（多个可能同时正确）例如：如果我有一个匹配whit id = 125并且我拥有所有猜测是错误的，除了8,11所以我应该更新所有具有匹配ID并且他们的猜测是8或11的匹配的结果字段（我将给这个结果字段1）并且我想给（2）另一个猜测同一场比赛

I use this query for all eleven possibilities like following:

我将此查询用于以下所有11种可能性：

UPDATE `tahminler` SET result=1 WHERE match_id='1640482' AND tahmin='8'
UPDATE `tahminler` SET result=1 WHERE match_id='1640482' AND tahmin='11'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='1'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='2'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='3'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='4'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='5'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='6'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='7'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='9'
UPDATE `tahminler` SET result=0 WHERE match_id='1640482' AND tahmin='10'

I want to know if I can do this job in one query?or not?

我想知道我是否可以在一个查询中完成这项工作？或不是？

3 个解决方案

#1

use these two query:

使用这两个查询：

UPDATE `tahminler` 
        SET result=0 
        WHERE match_id='1640482' 
              AND tahmin IN ('1','2','3','4','5','6','7','9','10')

And then use this:

然后用这个：

UPDATE `tahminler` 
        SET result=1 
        WHERE match_id='1640482' 
              AND tahmin IN ('8','11')

#2

You can do this, but it will be also ugly. Use CASE() operator, like:

你可以这样做，但也会很难看。使用CASE（）运算符，如：

UPDATE tahminler
SET
  result=CASE
    WHEN tahmin IN ('1','2','3','4','5','6','7','8','9','10') THEN 0
    WHEN tahmin IN ('8', 11) THEN 1
  END
WHERE
  match_id='1640482'

#3

UPDATE `tahminler` SET `result` = CASE WHEN tahmin IN(8,11) THEN 1
                                   WHEN tahmin IN(2,3,4,5,6,7,9,10) THEN 0
                              END
 WHERE match_id = 1640482;

#1