Thursday, March 29, 2007

mySQL Error 1241

I was recently coding a PHP application that connected to and interacted with a mySQL server. One of my sql scripts was producing a mySQL Error 1421 that I had not seen before. Here is the sql that I was running, the error, the resolution, and the reason.

The SQL:
INSERT INTO my_table(
 field1, field2, field3, field4, field5
)
SELECT (
 fieldA, fieldB, 1234, NOW(), fieldC
)
FROM my_table_two
WHERE my_table_two.PK =1


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

The resolution: Remove the Parentheses () around the second embedded SELECT statement so the statement is as follows.
INSERT INTO my_table(
 field1, field2, field3, field4, field5
)
SELECT
 fieldA, fieldB, 1234, NOW(), fieldC
FROM my_table_two
WHERE my_table_two.PK =1


The reason the error occurs: When you ad parentheses () around the SELECT columns you create an ordered list of values or a tuple. The SELECT statement expects a list of individual values. Or in simpler terms you are giving it the array[1][2] when it expects the values 1 and 2. You can recreate this error by running this sql statement SELECT 1,2 which will return 1 2 amazingly. Then run SELECT (1,2) which will return ERROR #1241 - Operand should contain 1 column(s). For me it occurred because I copied and pasted code from an insert statement to not have to retype all the fields and grabbed a little too much in my copy.

The Cliff Notes Version If you get a mySQL ERROR 1241 then look for some unnecessary parentheses surrounding your SELECT statement and remove them!

[tags]mySQL,1241,Error[/tags]

Labels: ,

1 Comments:

Blogger Daniel said...

Ah, for me it was because I needed to add a column alias after the subquery parenthesis (it was a SELECT query though)

9:06 PM  

Post a Comment

<< Home