MySQL Update查询具有多个值 [英] MySQL Update query with multiple values

问题描述

我在数据库中有一个表，记录如下:

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

我的用户为每个匹配项选择一个猜测，并且我有一个函数可以根据匹配结果来计算猜测的结果: 如果猜对了，结果将是(1) 如果是错误的结果将是(2) 如果比赛还没有结束，结果将是(默认为0) 我有11种猜测的可能性(一次可能是正确的) 例如:如果我有一个匹配的惠特id = 125，并且除了8,11以外，我所有的猜测都是错误的 因此，我应该为所有具有匹配ID且其猜测为8或11的匹配项更新结果字段(我将为此结果字段提供1) 而我想给(2)进行相同比赛的其他猜测

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

我将这个查询用于所有11种可能性，如下所示:

I use this query for all eleven possibilities like following:

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?

推荐答案

使用以下两个查询:

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

然后使用它:

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

这篇关于MySQL Update查询具有多个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！