-1

We have this in a table:

TABLE A

ID      DAY   CALL
mdma     1     A
mdma2    2     B
mdma3    3     C

So I want to update all fields CALL based in their ID.

UPDATE A SET CALL = 'D' WHERE ID IN ('mdma','mdma2', 'mdma3');

I'm getting errors on DataStudio, and I cannot run it. And we have a debate with this WHERE IN clause. Is this a valid argument or not?

Thank you in advance.

Edit: 

ERROR:

An unexpected token ")" was found following "mdma', 
".  Expected tokens may include:  "<value_expr>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.16.53
Marc B
  • 356,200
  • 43
  • 426
  • 500
DormantDemon
  • 57
  • 1
  • 8
  • 3
    Yes, you can. Can you show us the error? – Vertig0 Dec 02 '14 at 18:30
  • yes, it's valid sql. what's the specific error? – Marc B Dec 02 '14 at 18:31
  • And that is the actual statement? it seems like you are missing a quote, maybe the query is inside some varible using single quotes? – Vertig0 Dec 02 '14 at 18:32
  • 4
    CALL is a reserved word. Needs backticks around it. There might be other problems. – Mike Sherrill 'Cat Recall' Dec 02 '14 at 18:33
  • I will check again, the thing is the current query is having like 50K records. Seems pretty obvious to me, but is just giving me headaches. – DormantDemon Dec 02 '14 at 18:34
  • How is an update statement with a syntax error giving you 50K records? – GolezTrol Dec 02 '14 at 18:35
  • @axor When you say your current query has 50k records, are you saying that the table you're updating has 50k records, or you have a `IN` statement with 50k entries? – Siyual Dec 02 '14 at 18:35
  • @MikeSherrill'CatRecall' It was just an example table/fields. Thanks for the note. – DormantDemon Dec 02 '14 at 18:36
  • 1
    @axor please show us the real statement and if you can, in the context, this is a sintax error, pretty sure some missing/extra quote – Vertig0 Dec 02 '14 at 18:37
  • 1
    @axor We can't figure out what the problem might be if you're only giving us an example query that is neither using the same table/fields nor generating an error. – Siyual Dec 02 '14 at 18:38
  • @Siyual UPDATE TABLE SET FIELD = 'X' WHERE ID IN (<50K IDs>); – DormantDemon Dec 02 '14 at 18:39
  • 1
    Yeah, no. Don't do that. Put them in a temp table and join to it or something. Even if it didn't fuss about a missing quote, you would still get an error about having too many conditions in the `IN` clause. And even if for some reason it didn't, performance would be murder... – Siyual Dec 02 '14 at 18:39
  • http://sqlfiddle.com/#!2/de7ef4/1/0 it works. however there is a limit of sorts on the size of the in clause (http://stackoverflow.com/questions/1532366/mysql-number-of-items-within-in-clause) – xQbert Dec 02 '14 at 18:44
  • 50k of an alphanumeric id isn't a good idea. Use chunks, a temp table or something. In that 50k you can fail a quote or your software is limiting the long of the string or a lot of other possible problems. By the way, there must be a reason to use a string like ID, but in a table with 50k+ rows, i strongly recommend you to use a numeric id. – Vertig0 Dec 02 '14 at 18:45
  • The error message doesn't really sound like MySQL. Are you sure you are using that? –  Dec 02 '14 at 18:55

1 Answers1

0

CALL is reserved word. you must quote it with backtick (“`”):

UPDATE A SET `CALL` = 'D' WHERE ID IN ('mdma','mdma2', 'mdma3');
Maxxi
  • 36
  • 2
  • In the comments OP said it was just an example. Good call BTW – Vertig0 Dec 02 '14 at 18:54
  • This is only a minor part of the issue (which is not clear from the question, but is made clear in the comments to it - the OP is trying to use 50,000+ IDs in the `IN` statement). – Ken White Dec 02 '14 at 18:54