mySQL Operand should contain 1 column error

Question

I am trying to run the query shown below to include the results in a report and am getting an Operand should contain 1 column error #1241. I have not been able to figure out the cause of this. If I run this part by itself I do not get any errors: (pct_return_1 * .25) + (pct_return_2 * .25) + (pct_return_3 * .15) + (pct_return_4 * .15) + (pct_return_5 * .2)

Here is the code:

DROP TABLE IF EXISTS temp_5;
CREATE TABLE temp_5 (
    date_value date default NULL,
    pct_return_portfolio double default NULL,
    pct_return_benchmark double default NULL);

INSERT INTO temp_5 (date_value, pct_return_portfolio, pct_return_benchmark)
SELECT
 (date_value,
  (pct_return_1 * .25) +
  (pct_return_2 * .25) +
  (pct_return_3 * .15) +
  (pct_return_4 * .15) +
  (pct_return_5 * .2)
  FROM Temp_4),
pct_return_6)
FROM temp_4;

JYelton · Answer 1 · 2009-08-26T16:42:31.453

0

Try removing the first occurrence of FROM Temp_4 (the one just after the second value in the subquery).

Edit: Also double check parenthesis groupings, as in this example:

INSERT INTO table2 (field1, field2, field3, field4) (SELECT 'value1 from user input', field1, field2, field3 from table1)

Which is given on this page regarding insert subqueries: http://dev.mysql.com/doc/refman/5.1/en/subqueries.html

edited Aug 26 '09 at 16:42

answered Aug 26 '09 at 16:22

JYelton

35,664
27
132
191

I tried your suggestion and below is my actual query. The query did not run successfully. I received the same error message. INSERT INTO temp_5 (date_value, pct_return_portfolio, pct_return_benchmark) SELECT (date_value, ( (pct_return_1 * .25) + (pct_return_2 * .25) + (pct_return_3 * .15) + (pct_return_4 * .15) + (pct_return_5 * .2) ), pct_return_6) FROM temp_4; – user163129 Aug 26 '09 at 17:09
In the example I found, the select statement or subquery is completely enclosed in parenthesis. So on your statement, place an open parenthesis before "SELECT" and another after "FROM temp_4" hopefully that works for you. Subqueries are picky! – JYelton Aug 26 '09 at 17:35

score 0 · Answer 2 · answered Feb 24 '11 at 17:37

Use:

INSERT INTO temp_5 
  (date_value, pct_return_portfolio, pct_return_benchmark)
SELECT date_value,
       pct_return_1 * .25 + pct_return_2 * .25 + pct_return_3 * .15 + pct_return_4 * .15 + pct_return_5 * .2,
       pct_return_6
  FROM temp_4;

Mathematical order of operations ensures that brackets/parenthesis are not necessary.

mySQL Operand should contain 1 column error

2 Answers2