ERROR 1241 (21000): Operand should contain 1 column(s) when executing query

Question


INSERT INTO People(Track_id_Reference) 
SELECT track_id 
FROM Tracks 
WHERE track_title IN (SELECT tracktitle 
                      FROM top100 
                      WHERE artist IN (SELECT p.People_name, t.artist 
                                       FROM People AS p 
                                       RIGHT JOIN top100 AS t 
                                       ON 
                                       p.People_name=t.artist 
                                       UNION DISTINCT 
                                       SELECT p.People_name, t.artist 
                                       FROM People AS p 
                                       LEFT JOIN top100 AS t 
                                       ON 
                                       p.People_name=t.artist));

The error I get is

ERROR 1241 (21000): Operand should contain 1 column(s)

subquery which unions returns 2 columns. How can I fix this?

Rather obviously, you can fix this by selecting only one column instead of two. — DCoder, Nov 25 '12 at 07:17

John Woo · Accepted Answer · 2012-11-25T06:44:24.463

you lack FROM clause

SELECT track_id 
FROM   tableName
WHERE track_title

so the full query would be

INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM                                      -- <<== add tableName here
WHERE track_title = (
                SELECT tracktitle
                FROM top100
                WHERE artist = (
                                SELECT p.People_name,
                                        t.artist
                                FROM People AS p
                                RIGHT JOIN top100 AS t
                                        ON p.People_name = t.artist

                                UNION

                                        DISTINCT
                                SELECT p.People_name,
                                        t.artist
                                FROM People AS p
                                LEFT JOIN top100 AS t
                                        ON p.People_name = t.artist
                                )
                );

another problem that will arise soon is the use of equal = sign on the result of the subquery, it is much safer to use IN than = because it will throw an exception if, for instance, the subquery will return more than one value. Example,

INSERT INTO People (Track_id_Reference)
SELECT track_id
FROM                                      -- <<== add tableName here
WHERE track_title IN (
                SELECT tracktitle
                FROM top100 .............

thanks for the fast reply, I have edited and posted an new error I get, can you please help — Anvesh Raavi, Nov 25 '12 at 07:15
when using `IN` clause, there should only be one column on the result of the subquery, eg `WHERE artist IN ( SELECT t.artist FROM People` — John Woo, Nov 25 '12 at 07:19

ERROR 1241 (21000): Operand should contain 1 column(s) when executing query

1 Answers1