Inserting with WHERE and AND, Error Code: 1241. Operand should contain 1 column(s)

Question

Getting an error saying: Error Code: 1241. Operand should contain 1 column(s)

INSERT INTO TBL_TESTER_INFO2 (
    board_id,
    tester_name,
    board_name, 
    config,
    operating_system,
    log_created
) VALUES (
    (SELECT
        bl.id as 'board_id',
        bl.tester_type,
        bl.board_name
    FROM TesterDeviceMatrix.TBL_BOARD_LIST bl
    WHERE bl.tester_type = 'UFLEX'
    AND bl.board_name = 'HSD-U'
    ), 
    'tester',
    'board',
    'slot',
    'windows',
    '2015-06-10 16:08:42'
);

Any help on this? There seems to be no syntax error.

score 3 · Accepted Answer · answered Jun 17 '15 at 04:07

3

Pretty sure you can't mix a SELECT statement with other values as part of an INSERT statement's VALUE set.

I'd just put the static values into the SELECT, eg

INSERT INTO TBL_TESTER_INFO2 (...)
SELECT
    bl.id,
    bl.tester_type,
    bl.board_name,
    'slot',
    'windows',
    '2015-06-10 16:08:42'
FROM TesterDeviceMatrix.TBL_BOARD_LIST bl
WHERE bl.tester_type = 'UFLEX'
AND bl.board_name = 'HSD-U';

answered Jun 17 '15 at 04:07

Phil

157,677
23
242
245

This works perfectly fine, thank you. Sorry it took a while to accept your answer. I'm actually just using this query inside a perl script. That's why I tried to do like what I did in my question because I'm using variables as the data needed. Once again, thank you. :) – kross Jun 17 '15 at 06:14

Venkatesh Panabaka · Answer 2 · 2015-06-17T04:41:48.777

Here is the syntax of insert select SQL.

INSERT INTO table_name1(id, name,address,contact_number) 
SELECT id, name, address, contact_number FROM table_name2;

In your SQL insert statement columns are not matching in select SQL.

I modified your SQL. I think it will execute

INSERT INTO TBL_TESTER_INFO2 (board_id,tester_name,board_name, config,operating_system,log_created) 
    SELECT bl.id AS 'board_id',bl.tester_type,bl.board_name,'slot','windows','2015-06-10 16:08:42'
    FROM TesterDeviceMatrix.TBL_BOARD_LIST bl
    WHERE bl.tester_type = 'UFLEX'
    AND bl.board_name = 'HSD-U'

Thank you.

Inserting with WHERE and AND, Error Code: 1241. Operand should contain 1 column(s)

2 Answers2