MySQL ERROR: UNKNOWN COLUMN IN ‘FIELD LIST’

in this tutorial, we’ll learn how to fix “UNKNOWN COLUMN IN ‘FIELD LIST'”. Sometimes, We’re are getting errors after each insert or update to the MySQL table.

The message is “Unknown column ‘column-name’ in ‘field list'” while this column was existing in this table.

The most common causes of the error “MySQL: Unknown column in field list” are listed in this tutorial.

unknown column in field list error?

The MySQL unknown column in field list error happens when you put a column name in your SQL script that can’t be found by MySQL.

I have created an employee table and inserted data as below:

The following error message is thrown by MySQL when you attempt to insert data into a column that doesn’t exist in the table:

Option 1: The column name is not found

Let’s create a SQL to insert data into the non-existence column:

INSERT INTO employees(emp_name) VALUES ('Tim');

The error below is because there’s no emp_name column in the employees table.

Option 2: Column value is not wrapped with Quotes

Sometimes, We didn’t use quotes for the string values and also throw the same errors.

INSERT INTO employees(emp_name) VALUES (Tim);

The error:

-- ERROR 1054 (42S22): Unknown column 'Tim' in 'field list'

The value Tim must be wrapped in quotations (” or “”). Your attempt to put values into the target column from a different column will be misinterpreted by MySQL.

Option 3: calling a variable without the @ symbol

The same error will throw if calling a variable without the @ symbol.

SET @name="Tim";
SELECT name;

The Error message:

-- ERROR 1054 (42S22): Unknown column 'name' in 'field list'

Option 4: MySQL Trigger issue

Sometimes, we have defined triggers into MySQL that have columns that do not exist, t ll trigger when any record insert into MySQL.

create trigger insert_on_emp
after insert
on employee for each row
begin
insert into department (name, dept) values (new.name, 'devops');
end##
delimiter ;

To solve the error I will need either rewrite the trigger and remove dept column from the insert command or alter table ‘department’ and add column ‘dept’.