SQL multiple values in Where = "N"

Question

I am going to execute SQL command on my multiplayer game, I don't wanna any game stop or something like that because I am going to lose players so everything must go smooth.

Its my command :

UPDATE `players`
   SET `mana` = `mana` + 500 WHERE `vocation` != "121;122;123;124;125;126" 
   AND `id` IN (SELECT `player_id` FROM `player_storage` WHERE `key` = 25128 AND `value` = 15 GROUP BY `player_id`);

I want to update every vocation that is not 121 OR 122 OR 123 OR 124 OR 125 OR 126

Frankly, I'm afraid that this is a mess. – Strawberry Dec 11 '19 at 20:13 — Strawberry, Dec 11 '19 at 20:13
uhm ty for editing :) – Adrian Beliar Jagłowski Dec 11 '19 at 21:13 — Adrian Beliar Jagłowski, Dec 11 '19 at 21:13

D Stanley · Accepted Answer · 2019-12-11T20:43:54.897

1

You can use NOT IN to filter values that are not in a list.

WHERE vocation NOT IN (121, 122, 123, 124, 125, 126)

edited Dec 11 '19 at 20:43

answered Dec 11 '19 at 20:10

D Stanley

149,601
11
178
240

score 0 · Answer 2 · answered Dec 11 '19 at 20:15

0

Use BETWEEN

     ....Where location
    not between 120 
    and 
    127

answered Dec 11 '19 at 20:15

Himanshu

3,830
2
10
29

1

While this will work for this particular situation, `NOT IN` would be closer to the original intent. – D Stanley Dec 11 '19 at 20:45
But adding manual values and that too at such frequencies would be a bad practice. This works for all consecutive data just that replace the between values – Himanshu Dec 11 '19 at 22:51
suppose vocation # 130 needs to be added to the list, or #125 removed - what do you do? I'm not saying it's a _terrible_ solution, but there no obvious relationship between these IDs to indicate that a `between` is a natural way to think of them - they just _happen_ to be consecutive. – D Stanley Dec 12 '19 at 13:32

score 0 · Answer 3 · answered Dec 11 '19 at 20:19

0

If you have the list as a string of values with delimiter ; you can use FIND_IN_SET():

WHERE FIND_IN_SET(`vocation`, REPLACE('121;122;123;124;125;126', ';', ',')) = 0

answered Dec 11 '19 at 20:19

forpas

160,666
10
38
76

SQL multiple values in Where = "N"

3 Answers3