Using an IN clause with MySQL with stated values

Question

I'm trying to write a query that updates rows in a table if a certain column has a value in a list I'm providing:

UPDATE MY_TABLE SET COL1 = 'xyz' WHERE COL2 IN ('x', 'y', 'z');

I'm getting a syntax error, but I know that this should be possible. It's essentially a single command to execute the following 3 commands:

UPDATE MY_TABLE SET COL1 = 'xyz' WHERE COL2 = 'x';
UPDATE MY_TABLE SET COL1 = 'xyz' WHERE COL2 = 'y';
UPDATE MY_TABLE SET COL1 = 'xyz' WHERE COL2 = 'z';

The values xyz are being set dynamically by the user, and there could be an arbitrary number of values (or I would just code it the long and awful way and be done with it. The only information I can find on the IN clause is concerned with subqueries. Can someone help me rewrite this query?

Many thanks.

Answer 1

Is that your full update query? It looks like it is missing the end part. Short of the missing bits, it looks fine. Can you send the actual error message you receive?

Answer 2

I am pretty sure that you do not need to quote the individual values.

UPDATE MY_TABLE SET COL1 = 'xyz' WHERE COL2 IN ('x, y, z');

This always gets me too.

And if the values are numeric, you don't need quotes at all.

Answer 3

You should post the precise error message. The error message will give a clue about what part of the query confused the parser.

Do some of the values x, y, z contain quotes as part of the value? You could have imbalanced quotes. For example the following is clearly a syntax error:

UPDATE MY_TABLE SET COL1 = 'xyz' WHERE COL2 IN ('O'Reilly', 'Smith', 'Jones');

Give some more information and I'll edit this answer with more troubleshooting suggestions.

Answer 4

So now that everyone is telling me to look for unbalanced quotations, I looked at the query more carefully, and found the following:

UPDATE BOOK SET INVOICE_ID TO '1' WHERE START_NUMBER IN (1)

which should be:

UPDATE BOOK SET INVOICE_ID = '1' WHERE START_NUMBER IN (1)

Thanks for all the help, though, but now I feel nice and dumb.