// MySQL error reference

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
Error message
Unknown column '%s' in '%s'

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 active is 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 ... USING and then qualifying the merged column.
  • In a trigger, missing NEW. or OLD. before the column.

How to fix it

  1. List the table's columns.

    SHOW COLUMNS FROM orders;
  2. Use quotes for string values and backticks only for names.

    SELECT * FROM users WHERE status = 'active';
  3. Repeat the expression in WHERE, or use HAVING or a derived table, instead of a select-list alias.

    SELECT price * qty AS total FROM order_lines HAVING total > 100;
  4. 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

More databases, tables and columns errors

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.

// fewer errors, faster fixes

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.