CODEWITHSUNDEEP
mysql
Malca
Malca

Reputation: 123

MySQL Error: #1241 - Operand should contain 1 column(s)

I want to update an auto increment b_id based on column house_id example:

id house_id b_id

+-------------------+
|  1   |  H1  |  1  |
|  2   |  H1  |  2  |
|  3   |  H1  |  3  |
|  4   |  H2  |  1  |
|  5   |  H3  |  1  |
+-------------------+

Tried with this code and successfully generated the b_id but does not update the table.

SELECT t.id,
       house_id, 
       (SELECT count(*) 
        FROM House 
        WHERE house_id = t.house_id 
          AND id <= t.id
       ) AS b_id 
FROM House t

So i tried with this code:

UPDATE House 
SET b_id = (SELECT t.id, 
                   house_id, 
                   (SELECT count(*) 
                    FROM House 
                    WHERE house_id = t.house_id 
                      AND id <= t.id
                   ) AS b_id 
            FROM House t);

But there is error #1241 - Operand should contain 1 column(s).

I explore on internet, but do not really understand and cannot related with my sql code. Any idea?

Upvotes: 0

Views: 2119

Answers (1)

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

You are having 3 columns in your select to update a single column and that is why you are getting error. You can use Joins for your update. If you are using MYSQL 8.0 you can use below query to update the count.

UPDATE
    house AS h
JOIN
    (SELECT id, house_id, row_number() over (partition by house_id) as cnt 
     from house) AS g
    on g.id =h.id
SET 
    h.b_id = g.cnt ;

Check Demo here

Also i have changed your query using joins

UPDATE
    house AS p
JOIN 
    (SELECT t.id,(SELECT count(*) FROM house 
                   WHERE house_id = t.house_id 
                    AND id <= t.id) AS b_id 
     FROM house t) AS g
ON p.id = g.id
SET 
    p.b_id = g.b_id ;

Demo with your query

Upvotes: 1

Related Questions