MySQL error 1054: Unknown column in field list
The statement refers to a column that doesn't exist in the tables it names, or one that isn't visible at that point in the query.
- Error number
1054- Name
ER_BAD_FIELD_ERROR- SQLSTATE
42S22- Category
- Databases, tables and columns
What it means
MySQL couldn't resolve a column name. The second part of the message says where: in 'field list' (the select list or SET), in 'where clause', in 'on clause', in 'order clause' and so on.
A classic case: a string written with double quotes in ANSI_QUOTES mode, or with backticks by mistake, is read as a column name.
Common causes
- A typo, or a column that was renamed or never added.
- Using a select-list alias in
WHERE, which runs before the select list is computed. - A string value written in backticks instead of single quotes, so
activeis read as a column name rather than the text 'active'. - Referring to a column of a table that isn't joined yet, or using
JOIN ... USINGand then qualifying the merged column. - In a trigger, missing
NEW.orOLD.before the column.
How to fix it
-
List the table's columns.
SHOW COLUMNS FROM orders;
-
Use quotes for string values and backticks only for names.
SELECT * FROM users WHERE status = 'active';
-
Repeat the expression in
WHERE, or useHAVINGor a derived table, instead of a select-list alias.SELECT price * qty AS total FROM order_lines HAVING total > 100;
-
If the column is new, add it.
ALTER TABLE orders ADD COLUMN shipped_at DATETIME NULL;
Reproduce it safely
CREATE TEMPORARY TABLE t (a INT); SELECT b FROM t; -- ERROR 1054 (42S22): Unknown column 'b' in 'field list'
Related errors
1146Table doesn't exist1052Column is ambiguous207SQL Server: Invalid column name42703PostgreSQL: column does not existORA-00904Oracle: invalid identifier
More databases, tables and columns errors
1049Unknown database1046No database selected1050Table already exists1051Unknown table1060Duplicate column name1091Can't DROP; check that column/key exists1305Function or procedure does not exist1442Trigger can't update its own table
The code and message text are from the MySQL 8.4 Error Message Reference. Browse the full MySQL error list or search every database error.
Work on MySQL in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for MySQL and the other databases you use.