Error: Operand should contain 1 column(s)

Question

I want to select a group of data from database by using the code below.

SELECT * FROM History INNER JOIN (SELECT MAX(id) as id FROM History GROUP BY home_id) last_updates ON last_updates.id = History.id

And the result, i get the group of data. Then i want to update field on the table based on the group of data and i use below code.

UPDATE History SET status = '0' WHERE (SELECT * FROM History INNER JOIN (SELECT MAX(id) as id FROM History GROUP BY home_id) last_updates ON last_updates.id = History.id)

I think my sql update code is correct, but i got

Error: Operand should contain 1 column(s).

Is there anything i need to change?

Does this answer your question? [MySQL error 1241: Operand should contain 1 column(s)](https://stackoverflow.com/questions/15820288/mysql-error-1241-operand-should-contain-1-columns) — Prabhjot Singh Kainth, Dec 02 '19 at 05:29
@Metal Your answer can be made to work, but you need to wrap the subquery, that is all. — Tim Biegeleisen, Dec 02 '19 at 05:29

score 1 · Accepted Answer · answered Dec 02 '19 at 05:28

Try an update self join:

UPDATE History h1
INNER JOIN
(
    SELECT home_id, MAX(id) AS max_id
    FROM History
    GROUP BY home_id
) h2
    ON h1.home_id = h2.home_id AND
       h1.id = h2.max_id
SET
    h1.status = 0;

This assumes that you want to zero out the status of every record per home_id group having the max id value.

Error: Operand should contain 1 column(s)

1 Answers1