CODEWITHSUNDEEP
mysql
Matt Bannert
Matt Bannert

Reputation: 28274

Error Code: 1136 Column count doesn't match value count at row 1) inside sp

i have some troubles filling a predefined table with a stored procedure. mytable has 6 fields: uid,testrun,exp1,exp2,exp3,weightedvalue where uid is an autoincrement PK. My sp contains an insert statement like: 

CREATE PROCEDURE test (IN testrun INT)
BEGIN
.... some declare statements ... 
INSERT  INTO exp_factors(testrun,exp1,exp2,exp3,weightedvalue) VALUES
(testrun, 
 exp1,
 exp2_1 + exp2_2,
 exp3_1 + exp3_2,
 exp1 * 0,2 + (exp2_1+exp2_2) * 0.5 + (exp3_1+exp3_2) * 0.3);


END

Unfortunately this results in the error stated in the title. I understand that I insert only 5 of 6 fields but obviously I do not want to enter the autoincrement PK uid manually. How can I enter my exp values to this table without passing on the autoincrement id. Of course I could just create a table without an extra PK, but that´s not what i want.

Thanks for any suggestions in advance!

Upvotes: 3

Views: 54086

Answers (2)

Praveen Kumar
Praveen Kumar

Reputation: 1539

The above exception is thrown when the number of columns in the insert statement is lesser than the number of values in the query

for example

INSERT INTO PERSON (PERSON_ID,FIRST_NAME,LAST_NAME,GENDER,BIRTH_DATE,CITY,STATE,COUNTRY,POSTAL_CODE) VALUES (NULL,'JHON','DOE','M','1988-06-17','7TH CROSS','HERE','THERE','WHERE','5600012');

Here the the number of values is 10 but the no of columns in the query is 9 thus the database throws the above exception.

to solve the problem we have to add STREET column to the query

INSERT INTO PERSON (PERSON_ID,FIRST_NAME,LAST_NAME,GENDER,BIRTH_DATE,STREET,CITY,STATE,COUNTRY,POSTAL_CODE) VALUES (NULL,'PRAVEEN','KUMAR','M','1988-06-17','7TH CROSS','BANGALORE','KARNATAKA','INDIA','560078');

Upvotes: 3

Mike
Mike

Reputation: 21659

You have a comma in the last line of your insert, making 6 columns instead of 5:

exp1 * 0,2 + (exp2_1+exp2_2) * 0.5 + (exp3_1+exp3_2) * 0.3
        ^

I guess that this should be a decimal point, not a comma.

Upvotes: 5

Related Questions