How to Fix Unknown Column in Field List in MySQL

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 Fix Unknown Column in Field List in MySQL