MySQL Error “Operand should contain 1 column” [dup

2019-01-25 10:38发布

This question already has an answer here:

I could find a lot of similar questions but no real solution for my problem.

My SQL query:

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.

5条回答
三岁会撩人
2楼-- · 2019-01-25 11:25
UPDATE ADRESSEN
SET EMAIL = 0
WHERE ID = (SELECT ID
FROM EIGENSCHAFTEN WHERE Kategorie = "BOUNCE" 
GROUP BY ID
HAVING COUNT(*) = 1)
查看更多
来,给爷笑一个
3楼-- · 2019-01-25 11:31

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 HAVING clause.

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 IN instead 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 1 to the subquery will work, but you might want to do some sorting or use MIN/MAX to specify which row you get.

查看更多
我想做一个坏孩纸
4楼-- · 2019-01-25 11:32

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 "=".

查看更多
等我变得足够好
5楼-- · 2019-01-25 11:37
WHERE ID IN (SELECT ID 
                FROM EIGENSCHAFTEN 
                WHERE Kategorie = "BOUNCE" 
                GROUP BY ID
                HAVING COUNT(*) = 1 )
查看更多
走好不送
6楼-- · 2019-01-25 11:42

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 ID but are returning two columns

查看更多
登录 后发表回答