MySQL Update query with multiple values

Question

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

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?

Code Lღver · Accepted Answer · 2013-11-01 13:02:01Z

5

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')

answered Nov 1, 2013 at 13:02

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alma Do · Accepted Answer · 2013-11-01 13:05:05Z

4

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

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'

answered Nov 1, 2013 at 13:05

Alma Do

37.4k10 gold badges81 silver badges106 bronze badges

1 Comment

Schalk Over a year ago

I think this is the more prefered option.. I like it when there is only one statement instead of multiple statements

Strawberry · Accepted Answer · 2013-11-01 13:07:15Z

1

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;

answered Nov 1, 2013 at 13:07

Strawberry

34k14 gold badges43 silver badges57 bronze badges

Collectives™ on Stack Overflow

MySQL Update query with multiple values

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related