asked on

MYSQL Error: 1136

MYSQL Error:

Error 1136: at line 11: Column count doesn't match value count at row 1

Dear Experts:

I am having the issue with this error concerning code 1136. I have specified the correct number count in the Primary Key which is No.

This count is not being recognized in the corresponding VALUES line, which contains only 4 field values corresponding with the 4 in primary key No. Please explain the reason for the occurrence.

Restriction: The current format must be followed.

Also, using -- or /* commentary in .sql files have also occurred with potential problem. Is there reason for this?

Chris Stanyon

This generally means you have a mismatch in the number of values against the number of columns, but I can't give you a specific answer without seeing your code.

Ann Lamiskie

ASKER

Hello Expert,

The following is code below:

create table addme
(No int(4)NOT NULL, 
location char(35)NOT NULL,
age int(2)NOT NULL,
gender char(7)NOT NULL,
fullname char(34)NOT NULL,
PRIMARY KEY(No));

INSERT into addme VALUES('Farmington','20','Female','Susan Bubble');
INSERT into addme VALUES('WinchesterFive','30','Male','William James');

UPDATE testtable SET age='24' WHERE fullname='Susan Bubble');

Open in new window

kenfcamp

(No int(4)NOT NULL,

Open in new window

I'm thinking this may be your problem

If you're not going to provide a value you should set it as an auto_incremented field

no int(4) NOT NULL AUTO_INCREMENT,

Open in new window

Chris Stanyon

OK. You have 5 columns, but you're only inserting data into 4 of them - hence the error.

You should insert data into all 5 columns explicitly or setup the Primary Key (No) as an AutoIncrement Column.

Ann Lamiskie

ASKER

Hello Experts,

Adding the AUTO_INCREMENT which automatically fills in increments from 1 to number of rows created results in same issue.

create table addme
(No int(4)NOT NULL AUTO_INCREMENT,
location char(35)NOT NULL,
age int(2)NOT NULL,
gender char(7)NOT NULL,
fullname char(34)NOT NULL,
PRIMARY KEY(No));

INSERT into addme VALUES('Farmington','20','Female','Susan Bubble');
INSERT into addme VALUES('WinchesterFive','30','Male','William James');

UPDATE testtable SET age='24' WHERE fullname='Susan Bubble');

Open in new window

As suggested based on the # of columns and # of field datase, I have changed the No to 5 now to include the Primary Key Column. There is a 1064 error now with line 3.

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'location char(35)NOT NULL,
age int(2)NOT NULL,
gender char(7)NOT NULL,
fullname ' at line 3

Open in new window

Chris Stanyon

Not sure what you mean by change the No to 5 to include the primary key. No one suggested that.

This is what your create table should look like:

create table addme
(No int(5)NOT NULL AUTO_INCREMENT,
location char(35)NOT NULL,
age int(2)NOT NULL,
gender char(7)NOT NULL,
fullname char(34)NOT NULL,
PRIMARY KEY(No));

Open in new window

And, because you're not inserting data explicitly into ALL columns, your insert statements will need to specify the column names:

INSERT into addme (location, age, gender, fullname) VALUES ('Farmington','20','Female','Susan Bubble');

Open in new window

Finally, your UPDATE statement is referring to a table called testtable, which seems to have nothing to do with the other code (your table name is addme)

FishMonger

When a field is defined as an int, you don't need to put quotes around the number.

FishMonger

I am having the issue with this error concerning code 1136. I have specified the correct number count in the Primary Key which is No.

This count is not being recognized in the corresponding VALUES line, which contains only 4 field values corresponding with the 4 in primary key No. Please explain the reason for the occurrence.

No int(4)NOT NULL,

I think you have a misunderstanding of what the 4 in that clause means. You seam to think it refers to the number of fields defined in the table. That is incorrect. It specifies the (bit) size the the int field.
Numeric Type Overview

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.