MySQL Error "Operand should contain 1 column" [duplicate]

Question

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.

Answer 1

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

Answer 2

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.

Answer 3

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