使用带有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:

我收到语法错误,但我知道这应该是可能的。它本质上是执行以下3个命令的单个命令:

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?

值xyz是由用户动态设置的,可能有任意数量的值(或者我只是用冗长而糟糕的方式对其进行编码并完成它。我可以在IN子句中找到的唯一信息是关注的有子查询。有人可以帮我改写这个查询吗?

Many thanks.

4 个解决方案

#1

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:

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

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.

提供更多信息,我将编辑此答案并提供更多故障排除建议。

#2

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.

感谢所有的帮助,但现在我感觉很好,很愚蠢。

#3

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?

这是您的完整更新查询吗?看起来它缺少了最终部分。缺少比特,看起来很好。你能发送收到的实际错误信息吗?

#4

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.

如果值是数字,则根本不需要引号。

#1