MySQL 错误“操作数应包含 1 列”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9707664/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL Error "Operand should contain 1 column"
提问by jacob
I could find a lot of similar questions but no real solution for my problem.
我可以找到很多类似的问题，但没有真正解决我的问题。
My SQL query:
我的 SQL 查询：
UPDATE ADRESSEN
SET EMAIL = 0
WHERE ID = (SELECT ID, COUNT(ID) AS COUNTER
FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE"
GROUP BY ID
HAVING COUNTER = 1)
The error code I receive is
我收到的错误代码是
#1241 - Operand should contain 1 column(s)
If I just use the query in the parentheses it works and the result is
如果我只是在括号中使用查询，它会起作用，结果是
ID | COUNTER
0002159 | 1
Where is my error? Thanks a lot for your help.
我的错误在哪里？非常感谢你的帮助。
回答by sak
The issue is your inner query is returning two columns. Modify your query like
问题是您的内部查询返回两列。修改您的查询，如
UPDATE ADRESSEN
SET EMAIL = 0
WHERE ID = (SELECT ID
FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE"
GROUP BY ID
HAVING COUNT(ID) = 1)
This should work.
这应该有效。
I have one more suggestion, are you sure that your inner query will always return one row? If you want EMAIL to be set with value 0 for multiple IDs returned by inner query I would recommend you use "IN" instead of "=".
我还有一个建议，你确定你的内部查询总是返回一行吗？如果您希望为内部查询返回的多个 ID 将 EMAIL 设置为值 0，我建议您使用“IN”而不是“=”。
回答by Ryan P
Your subquery contains two columns. Try this:
您的子查询包含两列。尝试这个：
UPDATE ADRESSEN
SET EMAIL = 0
WHERE ID = (SELECT ID
FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE"
GROUP BY ID
HAVING COUNT(ID) = 1)
I removed COUNT(ID)so you only select the ID, and put that instead in your HAVINGclause.
我删除了COUNT(ID)所以你只选择了 ID，然后把它放在你的HAVING子句中。
Also, unless you are sure this query will never return more than one row, you need to deal with the possibility of duplicates. Either change to WHERE ID INinstead of WHERE ID =, or limit the number of results returned by the query. The method to limit the results will depend on your requirements - adding LIMIT 1to the subquery will work, but you might want to do some sorting or use MIN/MAXto specify which row you get.
此外，除非您确定此查询永远不会返回多于一行，否则您需要处理重复的可能性。要么WHERE ID IN改为WHERE ID =，要么限制查询返回的结果数。限制结果的方法将取决于您的要求 - 添加LIMIT 1到子查询将起作用，但您可能想要进行一些排序或使用MIN/MAX来指定您获得的行。
回答by James C
The problem is with your subquery:
问题出在您的子查询上：
SELECT ID, COUNT(ID) AS COUNTER FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE" GROUP BY ID HAVING COUNTER = 1
you're trying to compare it to IDbut are returning two columns
您正在尝试将其与它进行比较，ID但返回了两列
回答by Teja
UPDATE ADRESSEN
SET EMAIL = 0
WHERE ID = (SELECT ID
FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE"
GROUP BY ID
HAVING COUNT(*) = 1)
回答by DRapp
WHERE ID IN (SELECT ID
FROM EIGENSCHAFTEN
WHERE Kategorie = "BOUNCE"
GROUP BY ID
HAVING COUNT(*) = 1 )
