DROP TABLE IF EXISTS student;
CREATE TABLE student(
bannerid VARCHAR(9) PRIMARY KEY NOT NULL ,
lastname VARCHAR(45) NOT NULL ,
firstname VARCHAR(45) NOT NULL ,
major VARCHAR(45) NOT NULL DEFAULT 'undeclared' ,
gpa DECIMAL(2) NOT NULL DEFAULT 0.00 ,
age INT NOT NULL DEFAULT 18 );
INSERT INTO student VALUES ('b00001111', 'smith', 'fred', 'computer science', 3.12, 20);
INSERT INTO student VALUES ('b00002222', 'jones', 'herb', 'computer science', 2.00, 19);
INSERT INTO student VALUES ('b00003333', 'chan', 'jackie', 'computer information systems', 3.50, 50);
INSERT INTO student VALUES ('b00004444', 'baker', 'al');
INSERT INTO student VALUES ('b00005555', 'booker', 'sue');
This results in the following error and I do not understand why. I want the last two INSERTs to use the default values.
MySQL Error 1136:Column count does not match value count at row 1
Remove the brackets for Values(..), i.e., it should be Values (..), (..) instead.
insert into user(username,password,email,created,last_updated)
values
('TEST USERNAME','TEST PASSWORD','Test@test.com',current_timestamp(),current_timestamp()),
('TEST USERNAME 2','TEST PASSWORD 2','Test2@test.com',current_timestamp(),current_timestamp());
From Docs, the syntax is:
INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE] [INTO] tbl_name [PARTITION (partition_name [, partition_name] ...)] [(col_name [, col_name] ...)] {VALUES | VALUE} (value_list) [, (value_list)] ... [ON DUPLICATE KEY UPDATE assignment_list]…
INSERT statements that use VALUES syntax can insert multiple rows. To
do this, include multiple lists of comma-separated column values, with
lists enclosed within parentheses and separated by commas. Example:INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
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 employees:
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 statement:
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 email field is empty:
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.
Download TablePlus for Mac.
Not on Mac? Download TablePlus for Windows.
On Linux? Download TablePlus for Linux
Need a quick edit on the go? Download TablePlus for iOS
One of the more common error message in MySQL goes like this: “ERROR 1136 (21S01): Column count doesn’t match value count at row 1“.
This error typically occurs when you’re trying to insert data into a table, but the number of columns that you’re trying to insert don’t match the number of columns in the table.
In other words, you’re either trying to insert too many columns, or not enough columns.
To fix this issue, make sure you’re inserting the correct number of columns into the table.
Alternatively, you can name the columns in your INSERT statement so that MySQL knows which columns your data needs to be inserted into.
The error can also occur if you pass the wrong number of columns to a ROW() clause when using the VALUES statement.
Example of Error
Suppose we have the following table:
+----------+----------+----------+ | column_0 | column_1 | column_2 | +----------+----------+----------+ | 1 | 2 | 3 | | 4 | 5 | 6 | +----------+----------+----------+
The following code will cause the error:
INSERT INTO t1 VALUES (7, 8, 9, 10);Result:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
In this case, I tried to insert data for four columns into a table that only has three columns.
We’ll get the same error if we try to insert too few columns:
INSERT INTO t1 VALUES (7, 8);Result:
ERROR 1136 (21S01): Column count doesn't match value count at row 1
Solution 1
The obvious solution is to insert the correct number of rows. Therefore, we could rewrite our code as follows:
INSERT INTO t1 VALUES (7, 8, 9);Result:
Query OK, 1 row affected (0.00 sec)
Solution 2
Another way of doing it is to explicitly name the columns for which we want to insert data. This technique can be used to insert less columns than are in the table.
Example:
INSERT INTO t1 (column_0, column_1) VALUES (7, 8);Result:
Query OK, 1 row affected (0.00 sec)
This method may result in a different error if there are any constraints that require a value to be passed for that column (for example, if the table has a NOT NULL constraint on that column). Therefore, you’ll need to ensure you’re complying with any constraints on the column when doing this.
Are you getting the error “Column count doesn’t match value count at row 1” in MySQL?
In this article, I’ll show you how to resolve this error, as well as what it means, and some examples.
Let’s take a look.
What Is the “Column count doesn’t match value count at row 1” Error?
When you try to run an INSERT statement to insert data into a table, you might get this error:
Column count doesn’t match value count at row 1
In short, it means the number of columns in your INSERT statement does not match the number of values in the INSERT statement. To resolve this, ensure the columns match.
But what does this mean?
Error Definition
The error “Column count doesn’t match value count at row 1” means that the number of columns does not match the number of rows in your INSERT statement.
To demonstrate this, we can create a simple table with a few columns:
CREATE TABLE product_test (
id INT(4),
product_name VARCHAR(20),
price INT(5)
);So, let’s try to insert a record into this table:
INSERT INTO product_test VALUES (1, 'Office Chair');When you run this query, you’ll get an error:
Error Code: 1136. Column count doesn't match value count at row 1
This error happened because the INSERT statement contained two values (1 and “Office Chair”), but the table has 3 columns. The third column, price, was not mentioned in the INSERT statement.
You’ll also get the same error if you specify the column names, but the number of columns does not match the number of values:
INSERT INTO product_test (id, product_name, price)
VALUES (2, 'Desk');When you run this query, you’ll get an error:
Error Code: 1136. Column count doesn't match value count at row 1
In this example, you have specified three columns (id, product_name, price) but only two values (2 for id, and ‘Desk’ for product_name).
The “at row 1” part of the error just refers to the line of the query you’re running. It usually says “row 1” because the INSERT statement is run as a single statement.
So how do you fix this error?
You can follow the steps I mention below to fix this error. If you want to improve how you use MySQL, check out my PDF cheat sheet here:
How to Resolve the Error
To resolve this “Column count doesn’t match value count at row 1” error, you have to ensure that the columns in the table or your INSERT statement match the values you are inserting.
The best way to do this is to specify the columns you want in your INSERT statement and ensure the VALUES clause matches the column list.
In this example, you specify both the column names and the values:
INSERT INTO product_test (id, product_name)
VALUES (1, 'Office Chair');This will insert the two values you want to insert and ensures that the right columns are used (id and product_name).
Or, you could specify all three columns and their values:
INSERT INTO product_test (id, product_name, price)
VALUES (1, 'Office Chair', 100);Three columns are mentioned (id, product_name, price) and three values are mentioned (1, ‘Office Chair’, 100).
If you run this statement, it’s successful.
You could exclude the column names from the INSERT statement, and run something like this:
INSERT INTO product_test VALUES (1, 'Office Chair', 100);This will work. However, it’s risky as the order of the columns in the table could change if you add more columns. If that happens, your statement would break.
Check For Triggers
If you’re sure that the columns and rows are matching in your INSERT statement, another area you can check are the database triggers.
Triggers are pieces of code that run on certain actions on your database. For example, a trigger may be set to run when you INSERT a record into a table, and the trigger inserts a record into another table.
If you have a trigger like this, you’ll get this “Column count doesn’t match value count at row 1” error, but the message doesn’t tell you the table name.
This might indicate it’s the table you’re inserting into, but it could be a table that the trigger is inserting into.
This is common when you’re storing audit records (keeping a history of changes to records in a different table).
So, check if you have any triggers on the table you’re inserting into, and if so, check the code to ensure the values match the columns.
Conclusion
Hopefully, this error doesn’t bother you anymore now you know what causes it and how to fix it.
If you want to easily remember the features and syntax in MySQL, grab my MySQL Cheat Sheet here.