在带有指定值的MySQL上使用IN子句 [英] Using an IN clause with MySQL with stated values

问题描述

我正在尝试编写一个查询，如果某个列在我提供的列表中具有值，则该查询将更新表中的行:

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

我遇到语法错误，但是我知道这应该可行.本质上，这是一个命令，可以执行以下3个命令:

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

xyz值是由用户动态设置的，并且可以有任意数量的值(或者我将用冗长而糟糕的方式对其进行编码，并使用它来完成.我在<上可以找到的唯一信息em> IN 子句与子查询有关.有人可以帮我重写此查询吗?

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?

非常感谢.

推荐答案

您应该发布 precise 错误消息.该错误消息将提供有关查询的哪个部分混淆了解析器的线索.

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

某些值x，y，z是否包含引号作为值的一部分?您可能会有不平衡的报价.例如，以下内容显然是语法错误:

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.

这篇关于在带有指定值的MySQL上使用IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！