John Mwaniki /   29 Nov 2021

[Solved]: Column count doesn't match value count at row 1

It is a common scenario to encounter the error "Column count doesn't match value count at row 1" when running an SQL insert query in MySQL.

But what does this error mean?

Let's say for instance we have a database table named "users". That table has 5 columns namely: first_name, last_name, email, phone, and date.

We can insert data into the table using the query below.

INSERT INTO users (first_name, last_name, email, phone, date) VALUES ('John', 'Doe', 'johndoe@gmail.com', '111-222-333444', '2021-11-29');

Output

You can also exclude the column names from the SQL INSERT statement.

INSERT INTO users VALUES ('John', 'Doe', 'johndoe@gmail.com', '111-222-333444', '2021-11-29');

Output

The above 2 SQL statements insert new rows in the table with no errors.

Let's now make slight changes in the above SQL statement as below:

INSERT INTO users (first_name, last_name, email, phone, date) VALUES ('John', 'Doe', 'johndoe@gmail.com', '111-222-333444');

Output

As the error itself suggests, and as you can see from our above query, the number of columns does not match the number of values. If the number of values is greater or less than the number of columns, MySQL doesn’t know which data to insert in which column and it throws back the error.

In our statement, we have specified 5 columns (first_name, last_name, email, phone, date). But we have only 4 values to insert ('John', 'Doe', 'johndoe@gmail.com', '111-222-333444') thus the occurs due to the count mismatch.

Also, if you decide not to specify the column names in the SQL statement but fail to match the columns in the database table to the values as below you will get an error.

INSERT INTO users VALUES ('John', 'Doe', 'johndoe@gmail.com', '111-222-333444');

Output

In our table, we have 5 columns but in our statement above, we have specified 4 values and hence the error "Column count doesn't match value count at row 1".

How to fix the error

1. If you omit the column names in the SQL statement, make sure that you include the values for all the columns in the same order they occur in the database table.

2. If you specify the column names for the columns you want to insert data into in your SQL INSERT statement, make sure that they match the values you are inserting.

To achieve this, counter-check by comparing the column names to their respective values in the INSERT statement. Count and make sure that the number columns is equal to the number of values and in the matching order, i.e if for instance, the first column name is first_name, also make sure that the first value is the first name eg. "John". Do this for the rest of the columns.

3. Another culprit mistake that often results in this problem is forgetting to add commas (,) in between the column names or values. Always make sure that you use commas to separate two column names or values.