MySQL error 1052: Column is ambiguous
A column name appears in more than one table of the query and wasn't qualified, so MySQL can't tell which one you mean.
- Error number
1052- Name
ER_NON_UNIQ_ERROR- SQLSTATE
23000- Category
- Syntax and statements
What it means
When two joined tables both have a column called, say, id, an unqualified id could mean either. MySQL refuses to guess and names the column and the clause (in field list, in where clause, ...).
Common causes
- Joining tables that share column names such as
id,nameorcreated_at, and using the bare name. - Adding a join to a working query, which brings in a second column with the same name.
How to fix it
-
Qualify the column with the table name or alias.
SELECT o.id, c.name FROM orders o JOIN customers c ON c.id = o.customer_id WHERE o.id > 100;
-
For shared join keys,
USING (col)merges them into one column you can name without a qualifier.
Related errors
1054Unknown column in field list209SQL Server: Ambiguous column nameORA-00918Oracle: column ambiguously defined42702PostgreSQL: column reference is ambiguous
More syntax and statements errors
1064You have an error in your SQL syntax1149SQL syntax error1055Expression isn't in GROUP BY1093Can't specify target table for update1136Column count doesn't match value count1242Subquery returns more than 1 row1248Every derived table must have its own alias1318Incorrect number of arguments
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.