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’.