Often database developers get error saying ‘unknown column’ while running SQL queries especially SELECT queries. In this article, we will learn about the common reasons for this problem and how to fix unknown column in field list in MySQL.
How to Fix Unknown Column in Field List in MySQL
There are several reasons why people get this error, we will look at the most common of them.
Let us say you have the following MySQL table.
mysql> create table sales(id int, created_at datetime, amount int); mysql> insert into sales(id, created_at, amount) values(1, '2022-11-15 03:00:00', 180), (2, '2022-11-16 05:30:00', 100), (3, '2022-11-17 04:15:00', 280), (4, '2022-11-18 02:40:00', 150); mysql> select * from sales; +------+---------------------+--------+ | id | created_at | amount | +------+---------------------+--------+ | 1 | 2022-11-15 03:00:00 | 180 | | 2 | 2022-11-16 05:30:00 | 100 | | 3 | 2022-11-17 04:15:00 | 280 | | 4 | 2022-11-18 02:40:00 | 150 | +------+---------------------+--------+
Column Does Not Exist
Let us say you try to insert value into column student in above table.
INSERT INTO sales(student_name) VALUES (Michael);
You will get an error saying ‘unknown column student_name’ in result. This is because the column student_name does not exist.
Not Using Single/ Double Quotes Around Strings
Let us say you use the following query.
INSERT INTO sales(created_at) VALUES (2022-11-16);
Another reason why you commonly get this problem is because you used a string without using quotes. For example, in the above query we have not wrapped date 2022-11-16 in quotes. So MySQL treats it as a column name and looks for it in the sales table, thereby giving an error.
Not Referencing Variables Correctly
Let us say you use a variable. You may get this error if you do not reference a variable correctly.
SET @Var="Hello"; SELECT Var; -- ERROR 1054 (42S22): Unknown column 'Var' in 'field list'
In the above example, you need to use symbol @ before Var variable.
Using Back Quotes instead of Single/Double Quotes
One more reason why people get this error is because they wrap string values in back quotes instead of single quotes as shown below.
UPDATE sales SET created_at = `2022-11-11` WHERE id = 1; -- ERROR 1054 (42S22): Unknown column '2022-11-11' in 'field list'
When you wrap a string in back quotes, MySQL treats it as a column name. In the above example, we have used back quotes around date values so we get an error. Use single quotes instead.
Using Different Table Names
You can also get this error if you have used different tables in SELECT and FROM clauses of your query.
select sales.id from orders -- ERROR 1054 (42S22): Unknown column 'sales.id' in 'field list'
In the above query, we use sales.id in SELECT clause while orders table in FROM clause.
Invisible Characters in Query
Often developers copy past SQL queries from another source. While doing so, your console or editor may introduce invisible or incompatible characters in your query, leading to an unknown column error. You can avoid this error by rewriting the query manually instead of copy pasting them.
How to Loop Through All Rows in Table
How to Combine Columns in MySQL
How to Get List of Stored Procedures and Functions in MySQL
How to Select Rows Where Dates Match Day in MySQL
How to Copy Row and Insert Into Same Table in MySQL