When trying to insert a new data row into a table, you might run into this error:
Column count doesn't match value count at row 1.
That error message typically means the number of values provided in the
INSERT statement is bigger or smaller than the number of columns the table has, while at the same time, you did not specify the columns to be inserted. So MySQL doesn’t know which data to insert in which column and it throws back the error.
For example, you have this table
CREATE TABLE employees ( emp_no int(11) NOT NULL, birth_date date NOT NULL, first_name varchar(14) NOT NULL, last_name varchar(16) NOT NULL, gender enum('M','F') NOT NULL, hire_date date NOT NULL, email text, PRIMARY KEY (emp_no);
And you try to insert a new data rows into that table with this
INSERT INTO employees VALUES('400000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11');
As you can see, there are 7 columns in the table
employees but you are providing only 6 values in the
INSERT statement. MySQL returns the error:
Column count doesn't match value count at row 1
To fix this
1. Provided the full required data
If you omit the column names when inserting data, make sure to provide a full row of data that matches the number of columns
INSERT INTO employees VALUES('400000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11', '[email protected]');
Or if the
INSERT INTO employees VALUES('800000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11', '');
2. Specify the columns to be inserted in case not all columns are going to have value.
INSERT INTO employees.employees (emp_no, birth_date, first_name, last_name, gender, hire_date) VALUES('400000', '1990-09-09', 'Joe', 'Smith', 'M', '2009-09-11');
Sometimes, all the values are provided but you still see this error, you likely forgot to use the delimiter between two particular values and make it appear as one value. So double-check the delimiter and make sure you did not miss any semicolon.
Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.
Not on Mac? Download TablePlus for Windows.
Need a quick edit on the go? Download TablePlus for iOS