mysql中的my.in,在具有指定值的MySQL中使用IN子句

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.

解决方案

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.